Query and count on jsonb column

Question

I'm new to the postgreSQL(9.5) Json world. Looking for help writing this query. Take this simplified table as an example.

CREATE TABLE activity_log (uri varchar,
                           data jsonb );

Example of data inside of 'data' column.

"{"ListingInputFilterBean":{"searchItems": [], "listingStatus": "ACTIVE"}"

"{"ListingInputFilterBean":{"searchItems": [{"name": "Dachshund", "type": "BREED"}], "listingStatus": "ACTIVE"}}"

"{"ListingInputFilterBean":{"searchItems": [{"name": "Lab", "type": "BREED"}, {"name": "Black Lab", "type": "CST"}], "listingStatus": "ACTIVE"}}"

The 'data' column is used to log specific sets of data for each URI call. In this case the searchItems array contain the items used in the search. I'm looking to write a query that finds the most searched for 'breed'. I'd like to count the number of times each 'name' is used when type is 'BREED'.

My initial approach was to pull back each of the 'searchItems'. Turn those into a row set using jsonb_to_recordset, but I quickly got in over my head when reading the documentation (sorry, I'm a noob).

Any suggestions on how to write that SQL?

What have you tried? You must be aware that we are not a free code service. You have to show some effort to solve the problem you have. Take a look into the json functions for postgresql, try something then change your question adding your attempt and where you fail. — Jorge Campos
– Jorge Campos, Commented Feb 15, 2016 at 18:38
Thank you again @Dmitry for the help. Its starting to make since. — Curtis
– Curtis, Commented Feb 15, 2016 at 20:45

Dzmitry Savinkou · Accepted Answer · 2016-02-15 20:32:12Z

3

WITH log_activity(data) AS ( VALUES
  ('{"ListingInputFilterBean":{"searchItems": [], "listingStatus": "ACTIVE"}}'::JSONB),
  ('{"ListingInputFilterBean":{"searchItems": [{"name": "Dachshund", "type": "BREED"}], "listingStatus": "ACTIVE"}}'::JSONB),
  ('{"ListingInputFilterBean":{"searchItems": [{"name": "Lab", "type": "BREED"}, {"name": "Black Lab", "type": "CST"}], "listingStatus": "ACTIVE"}}'::JSONB)
)
SELECT search_item->>'name',count(search_item->>'name')
FROM
  log_activity la,
  jsonb_array_elements(la.data#>'{ListingInputFilterBean,searchItems}') as search_item
WHERE search_item->>'type' = 'BREED'
GROUP BY search_item;

Result:

   name    | count 
-----------+-------
 Lab       |     1
 Dachshund |     1
(2 rows)

Here you just need to iterate over the list of searchItems and group only those entries, which do match your criteria. Steps are the following:

Get jsonb array of searchItems with #> operator, it will get JSON object at specified path;
Iterate over the list of elements retrieved from step 1 with jsonb_array_elements(), function which expands a JSON array to a set of JSON values;
count() names where searchItems' type = BREED, you can get actual text value with ->> operator;

UPDATE

With jsonb_to_recordset() it looks shorter, but you need explicitly define search_item columns' types:

SELECT search_item.name ,count(search_item.name)
FROM
  log_activity la,
  jsonb_to_recordset(la.data#>'{ListingInputFilterBean,searchItems}') as search_item(name text,type text)
WHERE search_item.type = 'BREED'
GROUP BY search_item.name;

edited Feb 15, 2016 at 20:32

answered Feb 15, 2016 at 19:23

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

1 Comment

Curtis Over a year ago

Thank you! Worked perfectly. Working from your answer, I also came up with this. Any idea which performs better?

select name, count(id) from activity_log,  jsonb_to_recordset(data#>'{ListingInputFilterBean,searchItems}') as x(id int, name varchar, type varchar ) WHERE type = 'BREED' group by name order by count desc

Collectives™ on Stack Overflow

Query and count on jsonb column

1 Answer 1

UPDATE

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related