Home > Net >  separate values from JSON in PostgreSQL
separate values from JSON in PostgreSQL

Time:04-19

I am working with a table that has one JSON column (JSONcolumn). The values in it appear like this:

["91601","85202","78746"]

Is there any way to get all the objects from that JSON list separated into rows. I want the result to be like this:

JSONcolumn
91601
85202
78746

I read a lot of answers on how to do it but the difference that I noticed is that in my case, the JSON contains a LIST and in the most of cases the people answered using queries that work if the JSON contains a DICT

CodePudding user response:

First it is JSON Array and Object not List and Dict. List and dict are Python terms.

Second the documentation has a whole section JSON operators and functions that cover this.

Third what you want is:

select * from json_array_elements_text('["91601","85202","78746"]');
 value 
-------
 91601
 85202
 78746

--Or

select * from json_array_elements('["91601","85202","78746"]');
  value  
---------
 "91601"
 "85202"
 "78746"


  • Related