Is there an operator that extracts xx from '{"xx":123}'::jsonb
?
In my data I always have only one key, so something like #>>'{$.*}'
that meets this requirement, perhaps exists.
PS: the function jsonb_each
is not the answer.
It is ugly, "an elephant to kill a fly". Let see the real-life use case.
CREATE or replace FUNCTION jreduxseq_to_text(
p_list jsonb, p_level int default 1, p_max_level int default 3
) RETURNS TABLE(
level integer,
ordinality bigint,
prefix text,
suffixes jsonb,
is_prefix boolean
) AS $f$
SELECT p_level, t.ordinality,
CASE WHEN t.is_prefix THEN t2.key ELSE t.value #>> '{}'
END as prefix,
CASE WHEN t.is_prefix THEN t2.value ELSE NULL
END as suffixes,
t.is_prefix
FROM (
SELECT ordinality, value,
jsonb_typeof(value)='object' AS is_prefix
FROM jsonb_array_elements(p_list) WITH ORDINALITY
) t, LATERAL ( -- BELOW UGLY! all for get a key!!
SELECT *
FROM jsonb_each(CASE WHEN t.is_prefix THEN t.value ELSE '{}'::jsonb END)
UNION
(SELECT '' as key, NULL::jsonb as value WHERE not(t.is_prefix))
) t2
$f$ LANGUAGE SQL IMMUTABLE;
SELECT * FROM jreduxseq_to_text(
'["123",{"df62":["6cV","6eM",{"3":["6f","1"]}]},{"G":["1","2"]},"H","X"]'
::jsonb);
See example
id | val |
---|---|
1 | {"xx": 123} |
Step by step:
select *
,val->>'xx' t
,jsonb_path_query_array(val,'$.keyvalue()') t2
,(jsonb_path_query_array(val,'$.keyvalue()'))[0]->'value' t2v
from test
id | val | t | t2 | t2v |
---|---|---|---|---|
1 | {"xx": 123} | 123 | [{"id": 0, "key": "xx", "value": 123}] | 123 |
Shortly:
select (jsonb_path_query_array('{"xx":123}'::jsonb,'$.keyvalue()'))[0]->'value' xx_value
from test
xx_value |
---|
123 |
Update1 For list(array) of objects
select value,ordinality,is_prefix
, elem[0]->>'key' prefix
, elem[0]->>'value' suffixes
from(
select *
,jsonb_path_query_array(t.value,'$.keyvalue()') elem
,jsonb_typeof(value)='object' AS is_prefix
from jsonb_array_elements('[{"xx":123},{"yy":124},{},{"N":null}]'::jsonb)WITH ORDINALITY t
)t
value | ordinality | is_prefix | prefix | suffixes |
---|---|---|---|---|
{"xx": 123} | 1 | t | xx | 123 |
{"yy": 124} | 2 | t | yy | 124 |
{} | 3 | t | null | null |
{"N": null} | 4 | t | N | null |
Update2
See fiddle with recursive query example.
Perhaps this query will help to avoid recursive function calls.