-- The shredder (docs/COL-SHRED.md): absent versus null, empty arrays, mixed types, nesting, numbers, text, limits CREATE EXTENSION pg_sift; CREATE EXTENSION pg_sift_test; CREATE FUNCTION sh(doc jsonb, spec text, max_elems int DEFAULT 0, max_bytes int DEFAULT 0) RETURNS TABLE (kind text, path text, parent int, ord int, key text, state text, val text) LANGUAGE sql AS $$ SELECT s.kind, s.path, s.parent, s.ord, s.key, s.state, s.val FROM sift._test_shred(doc, spec, max_elems, max_bytes) s $$; -- absent, null, a value, and what is on the way SELECT * FROM sh('{"a":{"b":1}}', 'a.b int4, a.c int4, d int4, e.f int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+--------+----- col | a.b | | 0 | | value | 1 col | a.c | | 0 | | absent | col | d | | 0 | | absent | col | e.f | | 0 | | absent | (4 rows) SELECT * FROM sh('{"a":{"b":null},"d":null,"e":null}', 'a.b int4, a.c int4, d int4, e.f int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+--------+----- col | a.b | | 0 | | null | col | a.c | | 0 | | absent | col | d | | 0 | | null | col | e.f | | 0 | | absent | (4 rows) SELECT * FROM sh('{"a":5,"e":"x"}', 'a.b int4, e.f int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+--------+----- col | a.b | | 0 | | absent | col | e.f | | 0 | | absent | (2 rows) SELECT * FROM sh('{"a":[{"b":1}]}', 'a.b int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- col | a.b | | 0 | | overflow | ovf | a.b | | | | overflow | 1 (2 rows) SELECT * FROM sh('[1,2]', 'a int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- col | a | | 0 | | overflow | ovf | a | | | | overflow | 1 (2 rows) SELECT * FROM sh('5', 'a int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+--------+----- col | a | | 0 | | absent | (1 row) SELECT * FROM sh('null', 'a int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+--------+----- col | a | | 0 | | absent | (1 row) SELECT * FROM sh('{}', 'a int4, b[] text, c.* int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+--------+----- col | a | | 0 | | absent | list | b[] | 0 | 0 | | absent | 0 list | c.* | 0 | 0 | | absent | 0 (3 rows) -- arrays: absent, null, empty, one element, not an array SELECT * FROM sh('{}', 'a[] int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+--------+----- list | a[] | 0 | 0 | | absent | 0 (1 row) SELECT * FROM sh('{"a":null}', 'a[] int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+----- list | a[] | 0 | 0 | | null | 0 (1 row) SELECT * FROM sh('{"a":[]}', 'a[] int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+----- list | a[] | 0 | 0 | | value | 0 (1 row) SELECT * FROM sh('{"a":[1]}', 'a[] int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+----- col | a[] | 0 | 0 | | value | 1 list | a[] | 0 | 0 | | value | 1 (2 rows) SELECT * FROM sh('{"a":7}', 'a[] int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- list | a[] | 0 | 0 | | overflow | 0 ovf | a[] | | | | overflow | 1 (2 rows) SELECT * FROM sh('{"a":{"x":1}}', 'a[] int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- list | a[] | 0 | 0 | | overflow | 0 ovf | a[] | | | | overflow | 1 (2 rows) SELECT * FROM sh('{"a":{"x":1}}', 'a[]{x int4}'); kind | path | parent | ord | key | state | val ------+-------+--------+-----+-----+----------+----- list | a[] | 0 | 0 | | overflow | 0 ovf | a[].x | | | | overflow | 1 (2 rows) -- arrays of objects: elements keep their position, correlated columns share the element SELECT * FROM sh('{"items":[{"category":"x","kind":"p","qty":1},{"category":"x","kind":"q"},{"category":"y","kind":"p","qty":3},5,null,[1],{}]}', 'items[]{category text, kind text, qty int4}'); kind | path | parent | ord | key | state | val ------+------------------+--------+-----+-----+----------+----- col | items[].category | 0 | 0 | | value | x col | items[].category | 0 | 1 | | value | x col | items[].category | 0 | 2 | | value | y col | items[].category | 0 | 3 | | absent | col | items[].category | 0 | 4 | | absent | col | items[].category | 0 | 5 | | overflow | col | items[].category | 0 | 6 | | absent | col | items[].kind | 0 | 0 | | value | p col | items[].kind | 0 | 1 | | value | q col | items[].kind | 0 | 2 | | value | p col | items[].kind | 0 | 3 | | absent | col | items[].kind | 0 | 4 | | absent | col | items[].kind | 0 | 5 | | overflow | col | items[].kind | 0 | 6 | | absent | col | items[].qty | 0 | 0 | | value | 1 col | items[].qty | 0 | 1 | | absent | col | items[].qty | 0 | 2 | | value | 3 col | items[].qty | 0 | 3 | | absent | col | items[].qty | 0 | 4 | | absent | col | items[].qty | 0 | 5 | | overflow | col | items[].qty | 0 | 6 | | absent | list | items[] | 0 | 0 | | value | 7 ovf | items[].category | | | | overflow | 1 ovf | items[].kind | | | | overflow | 1 ovf | items[].qty | | | | overflow | 1 (25 rows) -- the same-element rule: an element with category x AND kind p exists in doc 1 only SELECT d, bool_or(c.state = 'value' AND c.val = 'x' AND k.state = 'value' AND k.val = 'p') AS one_element, bool_or(c.val = 'x') AND bool_or(k.val = 'p') AS across_elements FROM (VALUES (1, '{"items":[{"category":"x","kind":"p"},{"category":"y","kind":"q"}]}'::jsonb), (2, '{"items":[{"category":"x","kind":"q"},{"category":"y","kind":"p"}]}')) v(d, doc), LATERAL (SELECT * FROM sh(v.doc, 'items[]{category text, kind text}') WHERE path = 'items[].category') c, LATERAL (SELECT * FROM sh(v.doc, 'items[]{category text, kind text}') WHERE path = 'items[].kind' AND ord = c.ord) k GROUP BY d ORDER BY d; d | one_element | across_elements ---+-------------+----------------- 1 | t | t 2 | f | t (2 rows) -- nested arrays of objects and arrays of arrays SELECT * FROM sh('{"o":[{"id":1,"l":[{"v":"a"},{"v":"b"}]},{"id":2},{"id":3,"l":[]},{"id":4,"l":[{"v":"c"}]}]}', 'o[]{id int4, l[]{v text}}'); kind | path | parent | ord | key | state | val ------+-----------+--------+-----+-----+--------+----- col | o[].id | 0 | 0 | | value | 1 col | o[].id | 0 | 1 | | value | 2 col | o[].id | 0 | 2 | | value | 3 col | o[].id | 0 | 3 | | value | 4 col | o[].l[].v | 0 | 0 | | value | a col | o[].l[].v | 0 | 1 | | value | b col | o[].l[].v | 3 | 2 | | value | c list | o[] | 0 | 0 | | value | 4 list | o[].l[] | 0 | 0 | | value | 2 list | o[].l[] | 1 | 2 | | absent | 0 list | o[].l[] | 2 | 2 | | value | 0 list | o[].l[] | 3 | 2 | | value | 1 (12 rows) SELECT * FROM sh('{"m":[[1,2],[],[3],null,4,[[5]]]}', 'm[][] int4'); kind | path | parent | ord | key | state | val ------+-------+--------+-----+-----+----------+----- col | m[][] | 0 | 0 | | value | 1 col | m[][] | 0 | 1 | | value | 2 col | m[][] | 2 | 2 | | value | 3 col | m[][] | 5 | 3 | | overflow | list | m[] | 0 | 0 | | value | 6 list | m[][] | 0 | 0 | | value | 2 list | m[][] | 1 | 2 | | value | 0 list | m[][] | 2 | 2 | | value | 1 list | m[][] | 3 | 3 | | null | 0 list | m[][] | 4 | 3 | | overflow | 0 list | m[][] | 5 | 3 | | value | 1 ovf | m[][] | | | | overflow | 1 (12 rows) SELECT * FROM sh('{"m":[[{"x":1},{"x":2}],[{"x":3}]]}', 'm[][]{x int4}'); kind | path | parent | ord | key | state | val ------+---------+--------+-----+-----+-------+----- col | m[][].x | 0 | 0 | | value | 1 col | m[][].x | 0 | 1 | | value | 2 col | m[][].x | 1 | 2 | | value | 3 list | m[] | 0 | 0 | | value | 2 list | m[][] | 0 | 0 | | value | 2 list | m[][] | 1 | 2 | | value | 1 (6 rows) -- wildcard maps: key dictionary, order of jsonb keys, members of other types SELECT * FROM sh('{"metrics":{"cpu":0.5,"io":2,"net":null,"mem":"high","disk":[1]}}', 'metrics.* float4'); kind | path | parent | ord | key | state | val ------+-----------+--------+-----+------+----------+----- col | metrics.* | 0 | 0 | io | value | 2 col | metrics.* | 0 | 1 | cpu | value | 0.5 col | metrics.* | 0 | 2 | mem | overflow | col | metrics.* | 0 | 3 | net | null | col | metrics.* | 0 | 4 | disk | overflow | list | metrics.* | 0 | 0 | | value | 5 ovf | metrics.* | | | | overflow | 3 (7 rows) SELECT * FROM sh('{"metrics":{"a":{"n":1,"m":"x"},"b":{"n":2},"c":3}}', 'metrics.*{n int4, m text}'); kind | path | parent | ord | key | state | val ------+-------------+--------+-----+-----+--------+----- col | metrics.*.n | 0 | 0 | a | value | 1 col | metrics.*.n | 0 | 1 | b | value | 2 col | metrics.*.n | 0 | 2 | c | absent | col | metrics.*.m | 0 | 0 | a | value | x col | metrics.*.m | 0 | 1 | b | absent | col | metrics.*.m | 0 | 2 | c | absent | list | metrics.* | 0 | 0 | | value | 3 (7 rows) SELECT * FROM sh('{"metrics":[1]}', 'metrics.* int4'); kind | path | parent | ord | key | state | val ------+-----------+--------+-----+-----+----------+----- list | metrics.* | 0 | 0 | | overflow | 0 ovf | metrics.* | | | | overflow | 1 (2 rows) SELECT * FROM sh('{"metrics":{}}', 'metrics.* int4'); kind | path | parent | ord | key | state | val ------+-----------+--------+-----+-----+-------+----- list | metrics.* | 0 | 0 | | value | 0 (1 row) SELECT * FROM sh('{"g":[{"k":{"a":1,"b":2}},{"k":{"b":3}}]}', 'g[]{k.* int4}'); kind | path | parent | ord | key | state | val ------+---------+--------+-----+-----+-------+----- col | g[].k.* | 0 | 0 | a | value | 1 col | g[].k.* | 0 | 1 | b | value | 2 col | g[].k.* | 1 | 2 | b | value | 3 list | g[] | 0 | 0 | | value | 2 list | g[].k.* | 0 | 0 | | value | 2 list | g[].k.* | 1 | 2 | | value | 1 (6 rows) -- duplicate keys collapse in the jsonb: the last one wins; keys are case sensitive and byte exact SELECT * FROM sh('{"a":1,"a":2,"A":3}', 'a int4, A int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+----- col | a | | 0 | | value | 2 col | A | | 0 | | value | 3 (2 rows) SELECT * FROM sh('{"é":1,"é":2}', '"é" int4, "é" int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+----- col | é | | 0 | | value | 1 col | é | | 0 | | value | 2 (2 rows) -- integers: exact, range, fractions and exponents SELECT * FROM sh('{"n":[3,3.0,3.5,1e2,-0,1E+2,12345678901234567890,9223372036854775807,9223372036854775808,-9223372036854775808,-9223372036854775809,"7",true,null,[1],{}]}', 'n[] int8'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+---------------------- col | n[] | 0 | 0 | | value | 3 col | n[] | 0 | 1 | | value | 3 col | n[] | 0 | 2 | | overflow | col | n[] | 0 | 3 | | value | 100 col | n[] | 0 | 4 | | value | 0 col | n[] | 0 | 5 | | value | 100 col | n[] | 0 | 6 | | overflow | col | n[] | 0 | 7 | | value | 9223372036854775807 col | n[] | 0 | 8 | | overflow | col | n[] | 0 | 9 | | value | -9223372036854775808 col | n[] | 0 | 10 | | overflow | col | n[] | 0 | 11 | | overflow | col | n[] | 0 | 12 | | overflow | col | n[] | 0 | 13 | | null | col | n[] | 0 | 14 | | overflow | col | n[] | 0 | 15 | | overflow | list | n[] | 0 | 0 | | value | 16 ovf | n[] | | | | overflow | 7 (18 rows) SELECT * FROM sh('{"n":[0,32767,32768,-32768,-32769,0.0000,1e0]}', 'n[] int2'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+-------- col | n[] | 0 | 0 | | value | 0 col | n[] | 0 | 1 | | value | 32767 col | n[] | 0 | 2 | | overflow | col | n[] | 0 | 3 | | value | -32768 col | n[] | 0 | 4 | | overflow | col | n[] | 0 | 5 | | value | 0 col | n[] | 0 | 6 | | value | 1 list | n[] | 0 | 0 | | value | 7 ovf | n[] | | | | overflow | 4 (9 rows) SELECT * FROM sh('{"n":[2147483647,2147483648,-2147483648,-2147483649]}', 'n[] int4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+------------- col | n[] | 0 | 0 | | value | 2147483647 col | n[] | 0 | 1 | | overflow | col | n[] | 0 | 2 | | value | -2147483648 col | n[] | 0 | 3 | | overflow | list | n[] | 0 | 0 | | value | 4 ovf | n[] | | | | overflow | 4 (6 rows) SELECT * FROM sh('{"n":[100000000000000000000000000000000000000000000000000000000000000000000000000000000000000,1e-5,0.1e1]}', 'n[] int8'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- col | n[] | 0 | 0 | | overflow | col | n[] | 0 | 1 | | overflow | col | n[] | 0 | 2 | | value | 1 list | n[] | 0 | 0 | | value | 3 ovf | n[] | | | | overflow | 4 (5 rows) -- floats: the value of the decimal text rounded like float4in / float8in, out of range is an overflow SELECT * FROM sh('{"n":[0.1,16777217,3.4028235e38,3.5e38,1e-45,1e-46,1e-40,123456789.123456789,-0.0,1e400,1e-400]}', 'n[] float4'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+--------------- col | n[] | 0 | 0 | | value | 0.1 col | n[] | 0 | 1 | | value | 1.6777216e+07 col | n[] | 0 | 2 | | value | 3.4028235e+38 col | n[] | 0 | 3 | | overflow | col | n[] | 0 | 4 | | value | 1e-45 col | n[] | 0 | 5 | | overflow | col | n[] | 0 | 6 | | value | 1e-40 col | n[] | 0 | 7 | | value | 1.2345679e+08 col | n[] | 0 | 8 | | value | 0 col | n[] | 0 | 9 | | overflow | col | n[] | 0 | 10 | | overflow | list | n[] | 0 | 0 | | value | 11 ovf | n[] | | | | overflow | 4 (13 rows) SELECT * FROM sh('{"n":[0.1,9007199254740993,1.7976931348623157e308,1e309,5e-324,1e-330,123456789.123456789,0.30000000000000004]}', 'n[] float8'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+------------------------- col | n[] | 0 | 0 | | value | 0.1 col | n[] | 0 | 1 | | value | 9.007199254740992e+15 col | n[] | 0 | 2 | | value | 1.7976931348623157e+308 col | n[] | 0 | 3 | | overflow | col | n[] | 0 | 4 | | value | 5e-324 col | n[] | 0 | 5 | | overflow | col | n[] | 0 | 6 | | value | 123456789.12345679 col | n[] | 0 | 7 | | value | 0.30000000000000004 list | n[] | 0 | 0 | | value | 8 ovf | n[] | | | | overflow | 4 (10 rows) SELECT * FROM sh('{"n":[0.1,0.2,0.3,1.5,-2.25,123456.789,999999999999999,99999999999999.9,0.00000000000001,0.000000000000001,-0.0,-0.5,12345678.12345678]}', 'n[] float8'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+------------------- col | n[] | 0 | 0 | | value | 0.1 col | n[] | 0 | 1 | | value | 0.2 col | n[] | 0 | 2 | | value | 0.3 col | n[] | 0 | 3 | | value | 1.5 col | n[] | 0 | 4 | | value | -2.25 col | n[] | 0 | 5 | | value | 123456.789 col | n[] | 0 | 6 | | value | 999999999999999 col | n[] | 0 | 7 | | value | 99999999999999.9 col | n[] | 0 | 8 | | value | 1e-14 col | n[] | 0 | 9 | | value | 1e-15 col | n[] | 0 | 10 | | value | 0 col | n[] | 0 | 11 | | value | -0.5 col | n[] | 0 | 12 | | value | 12345678.12345678 list | n[] | 0 | 0 | | value | 13 (14 rows) SELECT * FROM sh('{"n":[1,2.5,"3",true]}', 'n[] float8'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- col | n[] | 0 | 0 | | value | 1 col | n[] | 0 | 1 | | value | 2.5 col | n[] | 0 | 2 | | overflow | col | n[] | 0 | 3 | | overflow | list | n[] | 0 | 0 | | value | 4 ovf | n[] | | | | overflow | 2 (6 rows) SELECT * FROM sh('{"n":[10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000]}', 'n[] float8'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- col | n[] | 0 | 0 | | overflow | list | n[] | 0 | 0 | | value | 1 ovf | n[] | | | | overflow | 4 (3 rows) -- bool and text SELECT * FROM sh('{"b":[true,false,null,1,"true",{}]}', 'b[] bool'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+------- col | b[] | 0 | 0 | | value | true col | b[] | 0 | 1 | | value | false col | b[] | 0 | 2 | | null | col | b[] | 0 | 3 | | overflow | col | b[] | 0 | 4 | | overflow | col | b[] | 0 | 5 | | overflow | list | b[] | 0 | 0 | | value | 6 ovf | b[] | | | | overflow | 3 (8 rows) SELECT * FROM sh('{"s":["abc","","héllo ✓","日本語",1,true,null,"abcdef","ééé"]}', 's[] text(6)'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+-------- col | s[] | 0 | 0 | | value | abc col | s[] | 0 | 1 | | value | col | s[] | 0 | 2 | | overflow | col | s[] | 0 | 3 | | overflow | col | s[] | 0 | 4 | | overflow | col | s[] | 0 | 5 | | overflow | col | s[] | 0 | 6 | | null | col | s[] | 0 | 7 | | value | abcdef col | s[] | 0 | 8 | | value | ééé list | s[] | 0 | 0 | | value | 9 ovf | s[] | | | | overflow | 10 (11 rows) SELECT * FROM sh('{"s":["x","tab\there","new\nline","quote\"q","back\\slash"]}', 's[] text'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+-------------- col | s[] | 0 | 0 | | value | x col | s[] | 0 | 1 | | value | tab here col | s[] | 0 | 2 | | value | new + | | | | | | line col | s[] | 0 | 3 | | value | quote"q col | s[] | 0 | 4 | | value | back\slash list | s[] | 0 | 0 | | value | 5 (6 rows) -- a text column holds 255 bytes unless declared longer SELECT state, length(val) FROM sh(jsonb_build_object('s', repeat('z', 255)), 's text') WHERE kind = 'col'; state | length -------+-------- value | 255 (1 row) SELECT state, length(val) FROM sh(jsonb_build_object('s', repeat('z', 256)), 's text') WHERE kind = 'col'; state | length ----------+-------- overflow | (1 row) SELECT state, length(val) FROM sh(jsonb_build_object('s', repeat('z', 256)), 's text(256)') WHERE kind = 'col'; state | length -------+-------- value | 256 (1 row) -- astext: the text `->>` gives for any scalar, a number as numeric_out prints it (not normalized), a container is a shape overflow SELECT * FROM sh('{"s":[1.50,-0.0,1e2,12345678901234567890,0.000001,"1.50","x",true,false,null,{"a":1},[1]]}', 's[] astext'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+---------------------- col | s[] | 0 | 0 | | value | 1.50 col | s[] | 0 | 1 | | value | 0.0 col | s[] | 0 | 2 | | value | 100 col | s[] | 0 | 3 | | value | 12345678901234567890 col | s[] | 0 | 4 | | value | 0.000001 col | s[] | 0 | 5 | | value | 1.50 col | s[] | 0 | 6 | | value | x col | s[] | 0 | 7 | | value | true col | s[] | 0 | 8 | | value | false col | s[] | 0 | 9 | | null | col | s[] | 0 | 10 | | overflow | col | s[] | 0 | 11 | | overflow | list | s[] | 0 | 0 | | value | 12 ovf | s[] | | | | overflow | 1 (14 rows) SELECT * FROM sh('{"s":1.50}', 's astext'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+------ col | s | | 0 | | value | 1.50 (1 row) SELECT * FROM sh('{"s":1.50}', 's text'); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- col | s | | 0 | | overflow | ovf | s | | | | overflow | 2 (2 rows) SELECT state, length(val) FROM sh(jsonb_build_object('s', repeat('z', 256)), 's astext') WHERE kind = 'col'; state | length ----------+-------- overflow | (1 row) -- limits: lists are cut at max_elems and max_bytes, the overflow is flagged on every column below SELECT * FROM sh('{"a":[1,2,3,4,5,6]}', 'a[] int4', 4); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- col | a[] | 0 | 0 | | value | 1 col | a[] | 0 | 1 | | value | 2 col | a[] | 0 | 2 | | value | 3 col | a[] | 0 | 3 | | value | 4 list | a[] | 0 | 0 | | overflow | 4 ovf | a[] | | | | overflow | 16 (6 rows) SELECT * FROM sh('{"a":[{"x":1,"y":"p"},{"x":2,"y":"q"},{"x":3,"y":"r"}],"b":[1]}', 'a[]{x int4, y text}, b[] int4', 2); kind | path | parent | ord | key | state | val ------+-------+--------+-----+-----+----------+----- col | a[].x | 0 | 0 | | value | 1 col | a[].x | 0 | 1 | | value | 2 col | a[].y | 0 | 0 | | value | p col | a[].y | 0 | 1 | | value | q list | a[] | 0 | 0 | | overflow | 2 list | b[] | 0 | 0 | | overflow | 0 ovf | a[].x | | | | overflow | 16 ovf | a[].y | | | | overflow | 16 ovf | b[] | | | | overflow | 16 (9 rows) SELECT * FROM sh('{"a":["aaaa","bbbb","cccc"]}', 'a[] text', 0, 9); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+------ col | a[] | 0 | 0 | | value | aaaa col | a[] | 0 | 1 | | value | bbbb col | a[] | 0 | 2 | | overflow | list | a[] | 0 | 0 | | value | 3 ovf | a[] | | | | overflow | 16 (5 rows) SELECT * FROM sh('{"m":{"a":1,"b":2,"c":3}}', 'm.* int4', 2); kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+----------+----- col | m.* | 0 | 0 | a | value | 1 col | m.* | 0 | 1 | b | value | 2 list | m.* | 0 | 0 | | overflow | 2 ovf | m.* | | | | overflow | 16 (4 rows) -- a spec the document shares nothing with, and nested declared paths that skip siblings SELECT * FROM sh('{"x":{"y":{"z":[1,2,3]}},"w":1}', 'q int4, x.y.z[] int4'); kind | path | parent | ord | key | state | val ------+---------+--------+-----+-----+--------+----- col | q | | 0 | | absent | col | x.y.z[] | 0 | 0 | | value | 1 col | x.y.z[] | 0 | 1 | | value | 2 col | x.y.z[] | 0 | 2 | | value | 3 list | x.y.z[] | 0 | 0 | | value | 3 (5 rows) -- bounded memory: a list is cut at max_elems (1 Mi by default) whatever the size of the array SELECT * FROM sift._test_shred_summary(jsonb_build_object('a', (SELECT jsonb_agg(i) FROM generate_series(1, 1100000) i)), 'a[] int4'); path | absent | nulls | vals | overflow | min | max ------+--------+-------+---------+----------+-----+--------- a[] | 0 | 0 | 1048576 | 0 | 1 | 1048576 (1 row) SELECT kind, path, state, val FROM sh(jsonb_build_object('a', (SELECT jsonb_agg(i) FROM generate_series(1, 20) i)), 'a[] int4', 5) WHERE kind <> 'col'; kind | path | state | val ------+------+----------+----- list | a[] | overflow | 5 ovf | a[] | overflow | 16 (2 rows) -- zero-length copies: an empty text and an empty member name as the first of their kind SELECT * FROM sh('{"s":""}', 's text') WHERE kind = 'col'; kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+----- col | s | | 0 | | value | (1 row) SELECT * FROM sh('{"m":{"":1,"a":2}}', 'm.* int4') WHERE kind = 'col'; kind | path | parent | ord | key | state | val ------+------+--------+-----+-----+-------+----- col | m.* | 0 | 0 | | value | 1 col | m.* | 0 | 1 | a | value | 2 (2 rows) -- the shredder is kept between documents: nothing of the previous one remains SELECT id, sift._test_shred_count(doc, 'items[]{category text, qty int4}, tags[] text, m.* int4') FROM (VALUES (1, '{"items":[{"category":"x","qty":1},{"category":"y","qty":2},{"category":"z"}],"tags":["a","b"],"m":{"a":1,"b":2}}'::jsonb), (2, '{}'), (3, '{"tags":["c"]}'), (4, '{"items":[{"qty":9}]}'), (5, '[1]'), (6, '{"m":{"a":1}}')) v(id, doc) ORDER BY id; id | _test_shred_count ----+------------------- 1 | 9 2 | 0 3 | 1 4 | 1 5 | 0 6 | 1 (6 rows) DROP FUNCTION sh(jsonb, text, int, int); DROP EXTENSION pg_sift_test; DROP EXTENSION pg_sift;