Aggregate rows according to JSON array content

Question

I have a PSQL table with json tags, that are always strings stored in a json array :

id     |  tags (json)
--------------------------
1      | ["tag1", "tag2"]
2      | ["tag12", "tag2"]
122    | []

I would like to query, for instance, the count of entries in the table containing each tag.

For instance, I'd like to get :

tag         |  count
--------------------------
tag1        | 1
tag2        | 2
tag12       | 1

I tried

SELECT tags::text AS tag, COUNT(id) AS cnt FROM my_table GROUP BY tag;

but if does not work, since it gives

tag                |  cnt
--------------------------
["tag1", "tag2"]   | 1
["tag12", "tag2"]  | 1

I guess I need to get the list of all tags in an inner query, and then for each tag count the rows that contain this tag, but I can't find how to do that. Can you help me with that ?

GMB · Accepted Answer · 2020-10-12 09:29:51Z

1

Use json[b]_array_elements_text() and a lateral join to unnest the array:

select x.tag, count(*) cnt
from mytable t
cross join lateral json_array_elements_text(t.tags) as x(tag)
group by x.tag

edited Oct 12, 2020 at 9:29

answered Oct 12, 2020 at 9:28

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

user330315 Over a year ago

I think json_array_elements_text() would be better - especially if the columns is a json and not jsonb column.

GMB Over a year ago

@a_horse_with_no_name: indeed... Thanks.

Collectives™ on Stack Overflow

Aggregate rows according to JSON array content

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related