Query values from each json in a json array on postgresql

Question

Each record in my database has a json list, from which I'm trying to return a value inside each element.

I'm trying to return

environmental services, energy & utilities

From

[
  {
    "key": "environmental services"
  },
  {
    "key": "energy & utilities"
  }
]

And.

construction

From

[
  {
    "key": "construction"
  }
]

Where the length of each json list in each record is undetermined.

I can get the first value with:

select
  column_name -> 0 -> 'key'
from table

But how to get all.

How can I do this in postgresql?

klin · Accepted Answer · 2019-05-27 15:10:58Z

1

Use the function jsonb_array_elements() in a lateral join to get all elements of the arrays and the aggregate function string_agg():

with my_table(json_col) as (
values
('[
  {
    "key": "environmental services"
  },
  {
    "key": "energy & utilities"
  }
]'::jsonb),
('[
  {
    "key": "construction"
  }
]')
)

select string_agg(value->>'key', ', ')
from my_table
cross join jsonb_array_elements(json_col)
group by json_col       -- use PK here (if exists)

                 string_agg                 
--------------------------------------------
 environmental services, energy & utilities
 construction
(2 rows)

answered May 27, 2019 at 15:10

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Query values from each json in a json array on postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related