postgresqljsonb

Looking for the operator that extracts the key of one of JSONB objects


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.


Note: why a table-function 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);
  1. Very complex SQL (LATERAL and CASE) to achieve something so simple.
  2. Side-effect of disappearing an entire row, requiring more artificial complexity with UNION.

Solution

  • 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

    fiddle

    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

    fiddle

    Update2 See fiddle with recursive query example.
    Perhaps this query will help to avoid recursive function calls.