postgres - querying for unique values in jsonb array field

Question

I have a table with a jsonb column 'metadata'. In the metadata column there are multiple key-value pairs so the table is formatted as such:

hash  metadata
1     {"type": ["A, B"],    "Name": ["One"]}
2     {"type": ["A, B, C"], "Name": ["Two"]}
3     {"type": ["B, C"],    "Name": ["Three"]}
4     {"type": ["B"],       "Name": ["Four"]}

I am trying to query the table so as to get the number of elements that contain each unique type, as such:

type  :  count
"A"   :   2
"B"   :   4
"C"   :   2

I have looked through the Postgres 9.6 documentation and many of the other stack overflow threads and tried a couple of things and the closest I can get is with this:

Select jsonb_array_elements(t.metadata -> 'Type') as type, count(DISTINCT t.hash)
FROM table AS t
GROUP BY type

which returns:

 type     :  count
"A, B"    :   1
"A, B, C" :   1
"B, C"    :   1
"B"       :   1

Is there any way to do this in postgresql?

Thanks

BShaps · Accepted Answer · 2018-07-23 17:48:52Z

1

Your metadata column is malformed. Right now it is an array of 1 item that is a string ["A, B"] when it should be ["A", "B"]

If you can't fix the data in the column, you can add a few more function calls around your type in the select

Select unnest(string_to_array(jsonb_array_elements(t.metadata -> 'Type'), ', ')) as type, count(DISTINCT t.hash)
FROM table AS t
GROUP BY type

edited Jul 23, 2018 at 17:48

answered Jul 23, 2018 at 17:42

BShaps

1,4248 silver badges18 bronze badges

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

1 Comment

BShaps Over a year ago

Sometimes all you need is a second set of eyes =)

Collectives™ on Stack Overflow

postgres - querying for unique values in jsonb array field

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