postgreSQL - how to filter by json

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.

CodePudding user response：

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.

CodePudding user response：

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.