Using SQLite Json Functions I want to retrieve a value base on the value near it-CodePudding

Lets say I have this Json and I would like to retrieve all the age values where the name equals Chris in the Array key.

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

That Json is present in the Json column inside my database. by that I would like to retrieve one age column the has the age 65 and 23 because they both named Chris.

CodePudding user response：

Use json_each() table-valued function to extract all the names and ages from the json array of each row of the table and json_extract() function to filter the rows for 'Chris' and get his age:

SELECT json_extract(j.value, '$.name') name, 
       json_extract(j.value, '$.age') age
FROM tablename t JOIN json_each(t.col, "$.Array") j
WHERE json_extract(j.value, '$.name') = 'Chris';

Change col to the name of the json column.

See the demo.