Search code examples
sqlarraysamazon-athenaprestotrino

How to select multiple values from JSON array

I have sample data like below, I want to display all project names, and not just one by passing index because the size of array is not known in every column.

By trying below SQL getting only 'project1'. Need both project1 and project2 in concatenation.

WITH dataset AS 
(
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS myblob
)
SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name
FROM dataset

Expecting like both project1 and project2 in concatenation.

project_name
project1,project2
Solution

  • There are multiple options. For example you can use the json_query which has better JSON path support than json_extract:

    SELECT json_query(myblob, 'lax $.projects[*].name' WITH  ARRAY WRAPPER) projects
FROM dataset;

    Output:

            projects
-------------------------
 ["project1","project2"]