CODEWITHSUNDEEP
sqlpostgresql
julian
julian

Reputation: 33

Join jsonb column with top level columns in Postgres

How can I join jsonb column value with top level values like ID for example

CREATE TABLE things (
    id SERIAL PRIMARY,
    data jsonb
);

If I run the following query: SELECT * FROM things;, I will get this

id | data
-----------
1  | {"key": "value}

How can I join get something with this format instead:

id | key
-----------
1  | value

i could do SELECT AS but, then i have to select all the keys manually and if one of the values are null its still returned as null value

id | key
-----------
1  | null

how can i select all jsonb data like i described above while ignoring null values?

Upvotes: 1

Views: 260

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

use the ->> operator

select id, data->>'key' as key from things;

Edit

Problem with that is that i have to select every key, and if key is null its still shown, can't i just select ...

filter it using another condition in where clause 

select id, data->>'key' as key from things where nullif(data->>'key','')
is not null;

Demo

Upvotes: 1

Related Questions