extraction for array in json

user_3072174 0
Viewed 48
CREATE TABLE example (j JSON);

INSERT INTO example VALUES
('{
    "family": "anatidae",
    "species": [
        {"name": "duck", "animal": true},
        {"name": "goose", "animal": true},
        {"name": "rock", "animal": false}
    ]
}'
);

How can I find if duck is one of the species?

It looks like I need to apply an extraction function to an array like:

SELECT 'duck' IN j -> '$.species' ->> 'name' AS is_duck_here FROM example
duckdb sql json
2 Answers

You can try with JSON_CONTAINS

select * from example where 
json_contains(json_extract(j,'$.species[*].name'),json_array('duck'));

dbFiddle

Test result: enter image description here

user_31764190

To extract values of array you need to unpack/UNNEST the values to separate rows and group/GROUP BY them back in a form that is required for the operation / IN / list_contains.

SELECT FIRST(j) AS j,
       list_contains(LIST(L), 'duck') AS is_duck_here
FROM (
    SELECT j,
           ROW_NUMBER() OVER() AS id,
           UNNEST(from_json(j->'species', '[\"json\"]'))->>'name' AS L
    FROM example
) GROUP BY id

I have not find any easier way of doing this so far.

user_30721740
Related