How to select single field from array of json objects?

Question

I have a JSONB column with values in following JSON structure

{
  "a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]
}

I need to select only b1 field in the result. So expected result would be

["value2", "value5"]

I can select complete array using query

select columnname->>'b' from tablename

S-Man · Accepted Answer · 2021-04-14 11:55:41Z

1

SELECT
    jsonb_agg(elements -> 'b1')                       -- 2
FROM mytable,
    jsonb_array_elements(mydata -> 'b') as elements   -- 1

a) get the JSON array from the b element (b) extract the array elements into one row each
a) get the b1 values from the array elements (b) reaggregate these values into a new JSON array

answered Apr 14, 2021 at 10:14

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Comments

user330315 · Accepted Answer · 2021-04-14 12:01:56Z

1

If you are using Postgres 12 or later, you an use a JSON path query:

select jsonb_path_query_array(the_column, '$.b[*].b1')
from the_table;

answered Apr 14, 2021 at 12:01

user330315