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

Question

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

@FrankHeikens How do I use COALESCE() with JSON operator as third column? because skills column value can be empty([]::json) or non-empty — overexchange
– overexchange, Commented Jun 27, 2024 at 2:00
@overexchange, Exclude the third row with an empty JSON array using json_array_elements function. Include it by using LEFT JOIN LATERAL clause for an empty array. SELECT t.id, t.name, COALESCE(array_agg(s.skill ->> 'description'), ARRAY[]::text[]) as skill FROM table1 t LEFT JOIN LATERAL json_array_elements(t.skills) AS s(skill) ON true GROUP BY t.id, t.name; — Deep Thumar
– Deep Thumar, Commented Jun 27, 2024 at 4:06
Does this answer your question? PostgreSQL jsonb_path_query removes result instead of returning null value .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. — Zegarek
– Zegarek, Commented Jun 27, 2024 at 5:17

Zegarek · Accepted Answer · 2024-06-27 05:58:38Z

2

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}

answered Jun 27, 2024 at 5:58

Zegarek

30.1k5 gold badges27 silver badges32 bronze badges

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

2 Comments

overexchange Over a year ago

In this syntax (select array_agg(e->>'description')from json_array_elements(skills)e) as skill I didn't get the syntax e)

overexchange Over a year ago

I use this syntax ARRAY(SELECT jsonb_array_elements_text( jsonb_path_query_array(skills::jsonb,'$[*].description') )) AS skills, that gives output [[22 33 44][22 33 44][22 33 44]] So, we see error in client code: pq unable to parse array expected ' {' at offset 0. How to get regular SQL arrays with curly brace {} to address this error?

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related