Postgresql - Counting Elements of a 2D JSON array

Question

I have a Postgresql JSON 2D array column containing string terms, e.g.:

Input

[["edwards", "block", "row"], ["edwards"], ["block"]]

Is it possible to compute the occurrence of each term purely in Postgresql? e.g.:

Output

Terms,                        Occur
["edwards", "block", "row"]   [2,2,1]

(Or in some similar format). Or would I have to compute the occurrences using a programming language?

Wolph · Accepted Answer · 2015-05-05 16:04:21Z

1

Here's one solution:

select array_to_json(array(
    select json_array_length(a.value)
    from json_array_elements('[["edwards", "block", "row"], ["edwards"], ["block"]]'::json) a
));

answered May 5, 2015 at 16:04

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

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

Comments

p_mcp · Accepted Answer · 2015-05-05 13:33:36Z

0

Figured this one out eventually. The query is as follows:

SELECT COUNT(t), t
FROM (SELECT jsonb_array_elements_text(json->'named_entities') AS t
FROM tweets WHERE event_id = XX) AS t1
GROUP BY t

answered May 5, 2015 at 13:33

p_mcp

2,8118 gold badges40 silver badges76 bronze badges

Collectives™ on Stack Overflow

Postgresql - Counting Elements of a 2D JSON array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related