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"