CODEWITHSUNDEEP
sqljsonpostgresql
Alex Melluzzo
Alex Melluzzo

Reputation: 73

How to query JSON column for unique object values in PostgreSQL

I'm looking to query a table for a distinct list of values in a given JSON column.

In the code snippet below, the Survey_Results table has 3 columns: Name, Email, and Payload. Payload is the JSON object to I want to query.

Table Name: Survey_Results

Name         Email                      Payload

Ying         [email protected]     [
                                      {"fieldName":"Product Name", "Value":"Calculator"},
                                      {"fieldName":"Product Price", "Value":"$54.99"}
                                      ]

Kendrick     [email protected]        [
                                      {"fieldName":"Food Name", "Value":"Texas Toast"},
                                      {"fieldName":"Food Taste", "Value":"Delicious"}
                                      ]

Andy         [email protected]   [
                                      {"fieldName":"Band Name", "Value":"MetalHeads"}
                                      {"fieldName":"Valid Member", "Value":"TRUE"}
                                      ]

I am looking for a unique list of all fieldNames mentioned.

The ideal answer would be query giving me a list containing "Product Name", "Product Price", "Food Name", "Food Taste", "Band Name", and "Valid Member".

Is something like this possible in Postgres?

Upvotes: 3

Views: 3336

Answers (1)

klin
klin

Reputation: 121624

Use jsonb_array_elements() in a lateral join:

select distinct value->>'fieldName' as field_name
from survey_results
cross join json_array_elements(payload)

  field_name   
---------------
 Product Name
 Valid Member
 Food Taste
 Product Price
 Food Name
 Band Name
(6 rows)

How to find distinct Food Name values?

select distinct value->>'Value' as food_name
from survey_results
cross join json_array_elements(payload)
where value->>'fieldName' = 'Food Name'

  food_name  
-------------
 Texas Toast
(1 row)

Db<>fiddle.

Important. Note that the json structure is illogical and thus unnecessarily large and complex. Instead of

[
    {"fieldName":"Product Name", "Value":"Calculator"},
    {"fieldName":"Product Price", "Value":"$54.99"}
]

use

{"Product Name": "Calculator", "Product Price": "$54.99"}

Open this db<>fiddle to see that proper json structure implies simpler and faster queries.

Upvotes: 5

Related Questions