Search code examples
postgresqljsonb

variable substitution querying postgres jsonb object

Hi I am querying a JSONb object where the search key depends on the value of another key/value pair. Consider the following example:

select 
'{"a":"b","b":2}'::jsonb->'a',--b,
('{"a":"b","b":2}'::jsonb->'a')::text,--"b"
'{"a":"b","b":2}'::jsonb->('{"a":"b","b":2}'::jsonb->'a')::text--null, desired output is 2

Somehow I need to dereference the "b" to a json search path like 'b'

Any suggestions will be greatly appreciated

Solution

  • You should tu use ->> operator instead ->. There was unwanted double quoting:

    select 
'{"a":"b","b":2}'::jsonb->'a',--b,
 ('{"a":"b","b":2}'::jsonb->>'a')::text,--"b"
'{"a":"b","b":2}'::jsonb->('{"a":"b","b":2}'::jsonb->>'a')::text;