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

postgreSQL – how to filter by json_agg value

I have join table between t_table and s_table.
there are many to many relationships between them.

s_table

id s_value
1 1
2 2
3 3

t_table

id t_value
1 100
2 200
3 300

t_id_s_id_table

s_id t_id
1 1
1 2
2 2
2 3
3 1
3 3

First, I aggregated t_value group by s_table id by this query

SELECT 
  t_id_s_id_table.s_id,
  JSON_AGG(t_value) AS json_agg
FROM
  t_id_s_id_table
LEFT JOIN
  t_table
ON
 t_table.id = t_id_s_id_table.t_id
GROUP BY 
 t_id_s_id_table.s_id

And I got this result.

s_id json_agg
1 100, 200
2 200, 300
3 100, 300

What I would like to do

I want to obtain all s_ids whose associated json_agg value includes 100.
(It means s_id = 1 and 3)

I tried the following query

  SELECT *
  FROM (
    SELECT 
      t_id_s_id_table.s_id,
      JSON_AGG(t_value) AS json_agg
    FROM
      t_id_s_id_table
    LEFT JOIN
      t_table
    ON
     t_table.id = t_id_s_id_table.t_id
    GROUP BY 
     t_id_s_id_table.s_id
  )
  WHERE COUNT(json_agg = 100) > 0

but it doesn’t work for me.
I got error operator does not exist: json = integer.

How can I make SQL in order to obtain get this result?
I am using PostgreSQL 11.2.
Thank you in advance.

Tags:

2

Answers


  1. 0

    Regardless of your query’s business logic – as you need to count how many json_agg array elements are equal to 100, your where clause shall be

    WHERE (
  select count(*) 
  from json_array_elements_text(json_agg) jae 
  where jae::integer = 100
) > 0

    or simpler, whether an array element equal to 100 exists

    WHERE exists (
  select from json_array_elements_text(json_agg) jae 
  where jae::integer = 100
)

    And btw better do not use the name of a function (json_agg) as a column name.

    Login or Signup to reply.
  2. 0

    The easy way would be to use HAVING instead of WHERE to act on each group, and use the BOOL_OR operator to compare if any item in the group is equal to 100;

    SELECT 
  t_id_s_id_table.s_id,
  JSON_AGG(t_value) AS json_agg
FROM
  t_id_s_id_table
LEFT JOIN
  t_table
ON
 t_table.id = t_id_s_id_table.t_id
GROUP BY 
 t_id_s_id_table.s_id
HAVING BOOL_OR(t_value=100)

    A DBfiddle to test with.

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

Back To Top
Search