How to select multiple values from JSON array

Question

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

Guru Stron · Accepted Answer · 2024-01-21 14:46:50Z

3

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"]

answered Jan 21, 2024 at 14:46

Guru Stron

150k11 gold badges186 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pavan Aithal Over a year ago

Thank you so much!!. But When Im trying with data getting this error- Cannot read input of type json as JSON using formatting JSON. Thanks in advance.

Guru Stron Over a year ago

@PavanAithal This query worked for me on the provided sample data. Note that unlike json_extract json_query can't work with JSON type, only with VARCHAR. You can try calling json_format on your column first.

Collectives™ on Stack Overflow

How to select multiple values from JSON array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related