CODEWITHSUNDEEP
arraysjsondatabasepostgresqljsonb
user2737876
user2737876

Reputation: 1168

PostgreSQL count results within jsonb array across multiple rows

As stated in the title, I am in a situation where I need to return a count of occurrences within an array, that is within a jsonb column. A pseudo example is as follows:

CREATE TABLE users (id int primary key, tags jsonb);
INSERT INTO users (id, j) VALUES
(1, '{"Friends": ["foo", "bar", "baz"]}'),
(2, '{"Friends": ["bar", "bar"]}');

Question:

For the example above, if I were to search for the value "bar" (given a query that I need help to solve), I want the number of times "bar" appears in the j (jsonb) column within the key "Friends"; in this case the end result I would be looking for is the integer 3. As the term "bar" appears 3 times across 2 rows.

Where I'm at:

Currently I have sql written, that returns a text array containing all of the friends values (from the multiple selected rows) in a single, 1 dimensional array. That sql is as follows 

SELECT jsonb_array_elements_text(j->'Friends') FROM users;

yielding result is the following:

jsonb_array_elements_text                                                  
-------------------------
foo
bar
baz
bar
bar

Other Details:

Please let me know if any additional information is needed, thanks in advance.

Upvotes: 2

Views: 1900

Answers (1)

user330315
user330315

Reputation:

You need to use the result of the function as a proper table, then you can easily count the number of times the value appears.

select count(x.val)
from users
  cross join lateral jsonb_array_elements_text(tags->'Friends') as x(val)
where x.val = 'bar'

Upvotes: 3

Related Questions