Extracting values from JSON list

Question

I have sample data like below, I want to display all project names, and not just one by passing index.

By trying below sql getting only 'project1'. Need both project1 and project2 in concatination.

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 concatination.

project_name
project1,project2

Answer 1

try the code below
WITH dataset AS (
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS myblob
)
SELECT CONCAT(
        json_extract_scalar(myblob, '$.projects[0].name'),
        ',',
        json_extract_scalar(myblob, '$.projects[1].name')
    ) AS project_names
FROM dataset;