CODEWITHSUNDEEP
jsonpostgresql
tidy2021
tidy2021

Reputation: 39

How to parse json array in postgres

I am trying to parse out the language for different profiles that are stored in a json field named "data". They are stored in their own array in the json field like:

"languages": ["EN", "BN", "HI"]

I can call the whole array by using:

data->>'languages' as languages

but I would like to split it out into

language1 = "EN"
language2 = "BN"
language3 = "HI"

I think if possible the best solution would be to return the whole language array but exclude "EN" from it, but I'm not sure if that is possible. ex.

"languages": ["BN", "HI"]

Upvotes: 0

Views: 117

Answers (1)

user330315
user330315

Reputation:

You can use the - operator to remove the EN key:

select (data -> 'languages') - 'EN' as languages
from the_table;

Online example

Upvotes: 1

Related Questions