Postgres select value by key from json in a list-CodePudding

Given the following:

create table test (
  id int, 
  status text
);

insert into test values 
(1,'[]'),
(2,'[{"A":"d","B":"c"}]'),
(3,'[{"A":"g","B":"f"}]');

Is it possible to return?

id  A     B
1   null  null    
2   d     c
3   g     f

I am attempting something like this:

select id, 
       status::json ->> 0 @> "A" from test

CodePudding user response：

Try this to address your specific example :

SELECT id, (status :: json)#>>'{0,A}' AS A, (status :: json)#>>'{0,B}' AS B
FROM test

see the result

see the manual :

jsonb #>> text[] → text

Extracts JSON sub-object at the specified path as text.

'{"a": {"b": ["foo","bar"]}}'::json #>> '{a,b,1}' → bar

CodePudding user response：

This does it:

SELECT id,
    (status::json->0)->"A" as A,
    (status::json->0)->"B" as B
FROM test;