CODEWITHSUNDEEP
sqldatabasepostgresqlarray-agg
overexchange
overexchange

Reputation: 1

Why array_agg() doesn't consider empty json column value?

Below is the postgres table table1:

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

with below 3 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

But below query

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

is missing third row from desired output.

Using SELECT query, How to include third row having empty json([]) ? to get desired output

Upvotes: 2

Views: 82

Answers (1)

Zegarek
Zegarek

Reputation: 26467

There's a jsonb_path_query_array() that can do it directly and get you a jsonb array, rather than a regular SQL array of text: demo at db<>fiddle

select
  id,
  name,
  jsonb_path_query_array(skills::jsonb,'$[*].description') as skill
from table1;

You can also use a scalar subquery. When those find no rows, they yield a null instead:

if, during a particular execution, the subquery returns no rows, there is no error; the scalar result is taken to be null

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

The reason it happened is that your use of the old-style comma , join with json_array_elements() set-returning function results in joining to an empty set for row 3. It's understood like this:

select id, name, array_agg(skill ->> 'description') as skill
from table1 inner join lateral json_array_elements(skills) AS skill on true
group by 1, 2;

An inner join even on true requires that there's something to join to. The json_array_elements() generates nothing for row 3, so that one has no rows to join to.
That can be illustrated as:

select id, name, array_agg(skill ->> 'description') as skill
from(values
     (1, 'Alice', '[{"sid" : 11, "description" : "Cardio"}, 
                    {"sid" : 13, "description" : "Anesthist"}]')
    ,(2, 'Bob',   '[{"sid" : 10, "description" : "Gastro"}, 
                    {"sid" : 9, "description" : "Oncology"}]')
    ,(3, 'Sam',   '[]') 
    )as table1(id, name, skills)
inner join(values
     (1, '{"sid" : 11, "description" : "Cardio"}')
    ,(1, '{"sid" : 13, "description" : "Anesthist"}')
    ,(2, '{"sid" : 10, "description" : "Gastro"}')
    ,(2, '{"sid" : 9, "description" : "Oncology"}') 
    --nothing for row 3
    )as gen(id,skill)
on table1.id=gen.id
group by 1, 2;

If you switch to an explicit join and make it a left join, you'll keep row 3:

select
  id,
  name,
  array_agg(skill ->> 'description') as skill
from table1 left join json_array_elements(skills) AS skill on true
group by 1, 2;
id name skill
2 Bob {Gastro,Oncology}
3 Sam {NULL}
1 Alice {Cardio,Anesthist}

Upvotes: 1

Related Questions