Leveraging the powerful SQLite JSON functions, my intention is to fetch a specific value by considering the neighboring values

Question

Leveraging the powerful SQLite JSON functions, my intention is to fetch a specific value by considering the neighboring values

Suppose I have a JSON structure and I want to extract the age values associated with the name "Chris" in the Array key.

{
    "Array": [
        {
            "age": "65",
            "name": "Chris"
        },
        {
            "age": "20",
            "name": "Mark"
        },
        {
            "age": "23",
            "name": "Chris"
        }
    ]
}

This JSON is stored in my database under the Json column. Therefore, I aim to retrieve the ages 65 and 23 since both individuals have the name Chris.

json sqlite select where-clause

Answer 1

Answer №1

To extract the names and ages from the json array in each row of the table, you can utilize the table-valued function called json_each(). Additionally, employ the json_extract() function to filter the rows specifically for the name 'Chris', allowing retrieval of his age:

The following query will accomplish this task:
SELECT json_extract(j.value, '$.name') as Name, 
       json_extract(j.value, '$.age') as Age
FROM your_table_name t JOIN json_each(t.json_column_name, "$.Array") j
WHERE json_extract(j.value, '$.name') = 'Chris';

In the above query, ensure that you replace 'your_table_name' with the actual name of your table, and substitute 'json_column_name' with the corresponding name of your JSON column.

If you require further clarification or examples, refer to the provided demo.

Answer 2

To extract the names and ages from the json array in each row of the table, you can utilize the table-valued function called json_each(). Additionally, employ the json_extract() function to filter the rows specifically for the name 'Chris', allowing retrieval of his age:

The following query will accomplish this task:
SELECT json_extract(j.value, '$.name') as Name, 
       json_extract(j.value, '$.age') as Age
FROM your_table_name t JOIN json_each(t.json_column_name, "$.Array") j
WHERE json_extract(j.value, '$.name') = 'Chris';

In the above query, ensure that you replace 'your_table_name' with the actual name of your table, and substitute 'json_column_name' with the corresponding name of your JSON column.

If you require further clarification or examples, refer to the provided demo.

Leveraging the powerful SQLite JSON functions, my intention is to fetch a specific value by considering the neighboring values

Answer №1

Similar questions

A guide to displaying the properties of a JSON document

Is it necessary to reset the variable following an ajax request?

Displaying data on the user interface in Angular by populating it with information from the form inputs

Python: decoding a byte stream string on the server end

The deployment of a module to the IoT edge device failed due to an error (ErrorCode:Argumentinvalid)

Create a key for QJsonObject using the current timestamp

Is the AJAX call returning JSON data?

Accessing a RESTful API using Django

Develop a MySQL function that generates a nested JSON Array data structure

A comparison between Protocol Buffers and the JSON and BSON formats

Showing intricate JSON data in AngularJS

Mapping an Optional<Enum> with @RequestBody in Spring's MVC

Using multi-dimensional JSON arrays for posting requests through Ajax

The rows sent to HTML/Bootstrap from Java through JSON do not neatly wrap across rows evenly

Using JSON to dynamically add data points to all series in Highcharts

The error message "Alamofire JSON POST response failed: Lost connection to the network" was displayed

Inserting a variable into a JSON string

Optimal Strategies for Interacting with a JSON REST API in Swift 2.0

Swift Parsing JSON

The execute command within a function is malfunctioning in Python 3.5