Getting values from all JSON fields in postgres

Question

I need a SQL query where I can select all json keys. The following query let's me get all the keys to the JSON field. But I'm a bit at loss how I would go about making a query to also get all the values out too.

SELECT DISTINCT ON (key.*) key.*
FROM my_table,
jsonb_object_keys(my_table.json_field) as key

So the result of the above query would simply just be

key1 
key2

With the following query you would get a result similar to this

SELECT * FROM my_table

| id | json_field |
| -- | ---------- |
| 1  | '{"key1": "value1"}' |
| 2  | '{"key2": "value2"}' |

The result I'm looking for would be the following

| id | key1   | key2   |
| -- | -------| ------ | 
| 1  | value1 | null   |
| 2  | null   | value2 |

What makes it difficult is that I don't know the names of all keys which also may be a lot of keys for a single row.

Please show your schema structure and sample input/output. Or at best dbfiddle.uk demo. — Lukasz Szozda
– Lukasz Szozda, Commented Aug 1, 2017 at 8:12

Clodoaldo Neto · Accepted Answer · 2017-08-01 08:52:19Z

1

select distinct on (field_1, field_2) id, job_id, field_1, field_2
from
    my_table,
    jsonb_populate_recordset(json_field) jprs (field_1 int, field2 text)

https://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

answered Aug 1, 2017 at 8:52

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

Jonathan Over a year ago

Thank you, I amended the question to better explain what I was after.

Collectives™ on Stack Overflow

Getting values from all JSON fields in postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related