Search code examples
arraysjsonsqlitejson-extract

SQLite JSON_EXTRACT All values of 1 object in an array

I have a column containing JSON arrays. The arrays are like this (table users, column user_info):

[
 {"email":"xyz@hotmail.com","user_key":"987654","name":"John Paul"},
 {"email":"abc@hotmail.com","user_key":"123456","name":"Tom Sawyer"},
 {"email":"1234@msn.com","user_key":"887645","name":"Bart Simpson"}
]

Some have 3 objects in each array, some have 20, some in between. I want to pull each "name" value from the arrays. So with the above example I want my query results to show:

John Paul
Tom Sawyer
Bart Simpson

I can do that with:

SELECT json_extract(users.user_info, '$[0]."name"', $[1]."name"', $[2]."name"')
FROM users

However, if I want to select ALL of them, no matter the length in each row of that column, how would I go about that so I don't have to list out each object number of the array?

Solution

  • You need the table-valued function json_each():

    SELECT json_extract(json_each.value, '$.name') name
FROM users u, json_each(user_info)

    If you want all the names of each row in a comma separated list you could use GROUP_CONCAT():

    SELECT GROUP_CONCAT(json_extract(json_each.value, '$.name')) name
FROM users u, json_each(user_info)
GROUP BY u.thread_id

    See the demo.