sqlarraysamazon-athenaprestotrino

Filtering out array elements if element contains specified string by SQL


I have sample data like below, I want to extract the elements which has only names and its completed keys not dep_names and its completed key.

I'm trying something like below,

WITH dataset AS 
(
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"dep_namename":"project1", "completed":false},{"name":"project2", "completed":false},{"name":"project3", "completed":true}]}'
     AS myblob
)
SELECT filter(json_query(myblob, 'lax $.projects[*]' with array wrapper),x -> x like '%name%')  AS project_name
FROM dataset

I'm expecting output like below

[
    {"name":"project2", "completed":false},
    {"name":"project3", "completed":true}
]

Solution

  • Try using exists filter directly in the query:

    SELECT json_query(myblob, 'lax $.projects[*]?(exists(@.name))' with array wrapper) AS project_name
    FROM dataset;
    

    Output:

                                     project_name                                 
    ------------------------------------------------------------------------------
     [{"name":"project2","completed":false},{"name":"project3","completed":true}] 
    (1 row)