Postgres select value by key from json in a list

Question

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

Edouard · Accepted Answer · 2021-11-30 19:59:14Z

1

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

answered Nov 30, 2021 at 19:48

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Roberts · Accepted Answer · 2021-11-30 19:45:08Z

0

This does it:

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

answered Nov 30, 2021 at 19:45

Tim Roberts

55.3k4 gold badges28 silver badges41 bronze badges

sqlfiddle.com/#!17/770b1/12... I think I am getting an error.

Interesting; that exact query worked for me with Postgres 9.5.