SELECT values from JSONB string in PostgreSQL

Question

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?

Laurenz Albe · Accepted Answer · 2020-03-27 10:40:12Z

1

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)

answered Mar 27, 2020 at 10:40

Laurenz Albe

257k22 gold badges310 silver badges387 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SELECT values from JSONB string in PostgreSQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related