How to parse json array in postgres

Question

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"]

score 1 · Accepted Answer · 2022-06-27 16:50:54Z

1

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

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

Online example

edited Jun 27, 2022 at 16:50

answered Jun 27, 2022 at 16:09

user330315

I get the logic you are using but I am getting an error message: No operator matches the given name and argument types. You might need to add explicit type casts. Do I need to change the data types?
– tidy2021
Commented Jun 27, 2022 at 16:34
@tidy2021: maybe data isn't defined as jsonb (or at least json)
– user330315
Commented Jun 27, 2022 at 16:50

Add a comment |

Collectives™ on Stack Overflow

How to parse json array in postgres

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related