Search code examples
arraysjsondatabasepostgresqljsonb

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"]}');
  • please note that the value for friends can contain the same value more than once. This will be relevant later (in this case the second value contains contains the name "bar" twice in jsonb column under the key "Friends".)

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
  • Given that this is an array, is it possible to filter this by the term "bar" in some fashion in order to get the count of the number of times it appears? Or am I way off in my approach?

Other Details:

  • Version: psql (PostgreSQL) 9.5.2
  • The table in question and a gin index on it.

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


Solution

  • 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'