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

Postgresql – postgres – How to parse json array elements?

Below is the postgres table table1:

CREATE TABLE table1 (
 id INT PRIMARY KEY,
 name TEXT,
 skills JSON
);

with below rows inserted:

INSERT INTO table1 (id, name, skills) VALUES
 (1, 'Alice', ‘[
                 {“sid" : 11, "description" : “Cardio"}, 
                 {"sid" : 13, "description" : “Anesthist"}
              ]'
 ),
 (2, ‘Bob', ‘[
               {“sid" : 10, "description" : “Gastro"}, 
               {"sid" : 9, "description" : “Oncology"}
              ]’
 ),
 (3, ‘Sam', ‘[
              ]’
 );

Below is the desired output, upon running select query:

id   name     skill
---------------------
1   Alice     [“Cardio”,“Anestisht”]
2   Bob       ["Gastro","Oncology"]
3   Sam       []

where skill column is TEXT type

Tried below query

select  
id, name, d ->> 'description' as skill 
from table1, 
json_array_elements(skills) as d

that gives duplicate rows(as shown below) with a missing row, which is incorrect

id   name     skill
---------------------
1   Alice     Cardio
1   Alice     Anestisht
2   Bob       Gastro
2   Bob       Oncology
Tags:

2

Answers


  1. 0

    Use array_agg() to aggregate the descriptions into an array:

    select
  id,
  name,
  array_agg(skill ->> 'description') as skill
from table1, json_array_elements(skills) AS skill
group by 1, 2

    See live demo.

    To return rows for people with no skills as well, here’s one way:

      id,
  name,
  array_agg(skill ->> 'description')
from table1, json_array_elements(skills) AS skill
group by 1, 2
union all
select
  id,
  name,
  array[]::text[]
from table1
where json_array_length(skills) = 0

    See live demo.

    Login or Signup to reply.
  2. 0

    Use jsonb_path_query_array(). It gets you a jsonb array as shown in your expected result, not a regular SQL array of text.
    demo at db<>fiddle

    select
  id,
  name,
  jsonb_path_query_array(skills::jsonb,'$[*].description') as skill
from table1;
    id name skill
    1 Alice ["Cardio", "Anesthist"]
    2 Bob ["Gastro", "Oncology"]
    3 Sam []

    If you’re on an older version of PostgreSQL (versions 9.3-11), you can expand and collapse the array in a scalar subquery, then coalesce() the null to an empty array:

    select
  id,
  name,
  coalesce((select json_agg(e->>'description')from json_array_elements(skills)e)
           ,'[]') as skill
from table1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search