CODEWITHSUNDEEP
sqlarrayspostgresqljsonb
Johannes42
Johannes42

Reputation: 29

SELECT values from JSONB string in PostgreSQL

I have a multiple jsonb strings in a table that where created by a third party app. I want to be able to separate the content into individual rows. A string looks like this:

[[ "Plant","p"],["Animal","gt"],["Lake","gf"],["Mountain","ke"]]

I need a Query that creates me the following:

Type    | Value
________________
Plant   | p
Animal  |gt
Lake    |gf
Mountain|ke

How do I do that?

Upvotes: 0

Views: 90

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246053

SELECT e ->> 0 AS type,
       e ->> 1 AS value
FROM jsonb_array_elements(
        '[[ "Plant","p"],["Animal","gt"],["Lake","gf"],["Mountain","ke"]]'::jsonb
     ) AS e;

   type   | value 
----------+-------
 Plant    | p
 Animal   | gt
 Lake     | gf
 Mountain | ke
(4 rows)

Upvotes: 1

Related Questions