How to query JSON column for unique object values in PostgreSQL

Question

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?

klin · Accepted Answer · 2019-08-10 10:01:43Z

5

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.

edited Aug 10, 2019 at 10:01

answered Jan 10, 2019 at 20:21

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Pranay Soni Over a year ago

how to find a distinct Food Name Value ?

Collectives™ on Stack Overflow

How to query JSON column for unique object values in PostgreSQL

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