Aggregate results based on array of strings in JSON?

Question

I have a table with a field called 'keywords'. It is a JSONB field with an array of keyword metadata, including the keyword's name.

What I would like is to query the counts of all these keywords by name, i.e. aggregate on keyword name and count(id). All the examples of GROUP BY queries I can find just result in the grouping occuring on the full list (i.e. only giving me counts where the two records have the same set of keywords).

So is it possible to somehow expand the list of keywords in a way that lets me get these counts?

If not, I am still at the planning stage and could refactor my schema to better handle this.

  "keywords": [
    {
      "addedAt": "2017-04-07T21:11:00+0000",
      "addedBy": {
        "email": "[email protected]"
      },
      "keyword": {
        "name": "Animal"
      }
    },
    {
      "addedAt": "2017-04-07T20:54:00+0000",
      "addedBy": {
        "email": "[email protected]"
      },
      "keyword": {
        "name": "Mammal"
      }
    }
  ]

S-Man · Accepted Answer · 2019-09-04 15:19:45Z

2

step-by-step demo:db<>fiddle

SELECT
    elems -> 'keyword' ->> 'name' AS keyword,             -- 2
    COUNT(*) AS count
FROM
    mytable t,
    jsonb_array_elements(myjson -> 'keywords') AS elems   -- 1
GROUP BY 1                                                -- 3

Expand the array records into one row per element
Get the keyword's names
Group these text values.

answered Sep 4, 2019 at 15:19

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Comment

S-Man Over a year ago

@deed02392 Did this help? In case, please do not forget to upvote and accept

Collectives™ on Stack Overflow

Aggregate results based on array of strings in JSON?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related