?

Log in

No account? Create an account

...

или внеочередные заметки


Wildcard query for jsonb
trekking, Himalaya
obartunov
Imagine, you want to find some value in jsonb data , but the only thing you know is the name of the key. For example, shop aggregator, which combines different hierarchies of goods, where some keys may be on different places.

Consider this unstructured data of type jsonb and query 'find something with "color":"red"'(I used this example several years ago to illustrate jsquery).

              Table "public.js_test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 value  | jsonb   |           |          |


SELECT * FROM js_test;
 id |                                 value
----+-----------------------------------------------------------------------
  1 | [1, "a", true, {"b": "c", "f": false}]
  2 | {"a": "blue", "t": [{"color": "red", "width": 100}]}
  3 | [{"color": "red", "width": 100}]
  4 | {"color": "red", "width": 100}
  5 | {"a": "blue", "t": [{"color": "red", "width": 100}], "color": "red"}
  6 | {"a": "blue", "t": [{"color": "blue", "width": 100}], "color": "red"}
  7 | {"a": "blue", "t": [{"color": "blue", "width": 100}], "colr": "red"}
  8 | {"a": "blue", "t": [{"color": "green", "width": 100}]}
  9 | {"color": "green", "value": "red", "width": 100}
(9 rows)



Variant 1 - Standard SQL (pretty complex)

WITH RECURSIVE t (id,VALUE) AS (
    SELECT
        *
    FROM
        js_test
    UNION ALL (
            SELECT
                t.id,
                COALESCE (kv.value, e.value) AS VALUE
            FROM
                t
            LEFT JOIN LATERAL jsonb_each ( 
                CASE
                    WHEN jsonb_typeof (t.value) = 'object' THEN t.value
                    ELSE NULL
                END
            ) kv
        ON TRUE
        LEFT JOIN LATERAL jsonb_array_elements ( 
                CASE
                   WHEN jsonb_typeof (t.value) = 'array' THEN t.value
                   ELSE NULL
                END
        ) e 
        ON TRUE
    WHERE
        kv.value IS NOT NULL
        OR e.value IS NOT NULL)
)
SELECT js_test.*
FROM (
        SELECT
            id
        FROM
            t
        WHERE
            VALUE @> '{"color": "red"}'
        GROUP BY id
) x
JOIN js_test ON js_test.id = x.id;

 id |                                 value
----+-----------------------------------------------------------------------
  2 | {"a": "blue", "t": [{"color": "red", "width": 100}]}
  3 | [{"color": "red", "width": 100}]
  4 | {"color": "red", "width": 100}
  5 | {"a": "blue", "t": [{"color": "red", "width": 100}], "color": "red"}
  6 | {"a": "blue", "t": [{"color": "blue", "width": 100}], "color": "red"}
(5 rows)



Variant 2: jsquery (elegant query, but not standard way)

SELECT * FROM js_test WHERE value @@ '*.color = "red"'::jsquery;



Variant 3: Use our extension (** for any level) of jsonpath specification to a new SQL/JSON from SQL-2016.

SELECT * FROM js_test WHERE JSON_EXISTS( value,'$.**.color ? (@ == "red")');
 id |                                 value
----+-----------------------------------------------------------------------
  2 | {"a": "blue", "t": [{"color": "red", "width": 100}]}
  3 | [{"color": "red", "width": 100}]
  4 | {"color": "red", "width": 100}
  5 | {"a": "blue", "t": [{"color": "red", "width": 100}], "color": "red"}
  6 | {"a": "blue", "t": [{"color": "blue", "width": 100}], "color": "red"}
(5 rows)
 id |                                value
----+----------------------------------------------------------------------
  5 | {"a": "blue", "t": [{"color": "red", "width": 100}], "color": "red"}
(1 row)


More general syntax includes specification of range of levels to search, for example:

SELECT * FROM js_test WHERE JSON_EXISTS( value,'$.**{0,}.color ? (@ == "red")');

Tags: , ,