CODEWITHSUNDEEP
sqlpostgresqljsonb
Jtt3mr
Jtt3mr

Reputation: 25

postgres - querying for unique values in jsonb array field

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

Upvotes: 0

Views: 967

Answers (1)

BShaps
BShaps

Reputation: 1414

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

Upvotes: 1

Related Questions