extraction for array in json

Question

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

It looks like I'm licking something like `[*]` - `j -> '$.species[*].name'` — Dmitry Petrov, Sep 04 '22 at 01:32
yes speoes is an array so you need to add which element you mean or like in your comment all of them — nbk, Sep 04 '22 at 01:46
I need all the elements :) to check if `duck` is in any of them — Dmitry Petrov, Sep 04 '22 at 01:49

score 1 · Answer 1 · answered Sep 11 '22 at 04:15

1

You can try with JSON_CONTAINS

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

dbFiddle

Test result:

answered Sep 11 '22 at 04:15

flyingfox

13,414
3
24
39

Beautiful solution, thanks! Unfortunately, it does not work in DuckDB that I use. But it seems like it works just fine in MySQL & PgSQL. I'll accept the solution once it implemented in DuckDB :) – Dmitry Petrov Sep 11 '22 at 16:16

score 0 · Answer 2 · answered Sep 11 '22 at 03:45

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.

score 0 · Answer 3 · answered Sep 01 '23 at 16:39

As far as I can tell duckdb does not currently support [*] in a json path; it seems to use a json library implementation from sqlite which does not support this functionality.

I created my own macro:

CREATE OR REPLACE MACRO json_array_extract_text(jsdata, jspath) AS (
    WITH jsp AS (
        SELECT x
        FROM (SELECT STR_SPLIT(jspath, '[*]') AS x)
    ), arraylen AS (
        SELECT
            json_array_length(jsdata, x[1]) AS len
        FROM jsp AS x
    ), jindex AS (
        SELECT RANGE(CAST(x.len AS BIGINT)) as ix
            FROM arraylen as x
    ), jindex_xpand AS (
        SELECT UNNEST(j.ix) AS index
        FROM jindex AS j 
    ), paths AS (
        SELECT x[1] || '[' || ix.index || ']' || x[2] AS path
        FROM jindex_xpand AS ix
        CROSS JOIN jsp AS x
        ORDER BY ix.index
    )

    SELECT ARRAY_AGG(json_extract_string(jsdata, p.path)) AS array
    FROM paths as p
);

It uses the json_array_length function to determine how many indices to build and then builds the paths by replacing [*] which each index. Would appreciate any clues in how to simplify this.

CREATE OR REPLACE TABLE example (j JSON);

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

SELECT list_contains(json_array_extract_text(e.j, '$.species[*].name'), 'duck')
FROM example AS e;

output

    list_contains(json_array_extract_text(e.j, '$.species[*].name'), 'duck')
0   True

extraction for array in json

3 Answers3