Question 332524 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get all the objects from a json array in postgres but selecting just certain properties?

I have a jsonb column in a table, and this colum (named services) contains an array of objects like this:

[{"id": "1", "service_id": "4100002"}, {"id": "2", "service_id": "4100003"}, {"id": "3", "service_id": "410004"}]

If this same table contains two more columns; id and name. How can i make a query so that the result turns out like this:

id name services
34 alpha 4100002, 4100003, 410003
23 betha 4100001, 4100005

Thank you!

Tags:

2

Answers


  1. 0

    You’re looking for a subquery over an expansion of the array into a set of rows:

    SELECT
  id,
  name,
  ARRAY(
    SELECT service_id
    FROM jsonb_to_recordset(services) AS s(service_id int)
  ) AS services
FROM my_table;
    Login or Signup to reply.
  2. 0

    Unpack the array and extract the field you want, then agg it back up:

    select
  id,
  name,
  string_agg(service, ', ') as services
from (
  select id,
  name,
  json_array_elements(named_service)->>'service_id' as service
  from mytable)
group by 1, 2

    See live demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search