Re: Extract data from JSONB

Hello

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Michael Paquier
> Sent: Montag, 8. August 2016 05:24
> To: Alex Magnum <magnum11200(at)gmail(dot)com>
> Cc: Postgres General <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] Extract data from JSONB
>
> On Mon, Aug 8, 2016 at 12:08 PM, Alex Magnum <magnum11200(at)gmail(dot)com> wrote:
> > How can I convert that into one row each based on status; for example
> > if I only want to have the active modules.
>
> You can use jsonb_each to decompose that:
> =# select key, (value::json)->'status' from jsonb_each('{
>
> "accounts":
> {"status": true},
> "admin": {"status": true},
> "calendar": {"status": false},
> "chat": {"status": true},
> "contacts": {"status": true},
> "dashboard": {"status": false},
> "help": {"status": true}}'::jsonb);
> key | ?column?
> -----------+----------
> chat | true
> help | true
> admin | true
> accounts | true
> calendar | false
> contacts | true
> dashboard | false
> (7 rows)

Building on that you just need to add a where clause, but I assume that was obvious.

select key, (value::json)->'status' from jsonb_each(
'{
"accounts": {"status": true},
"admin": {"status": true},
"calendar": {"status": false},
"chat": {"status": true},
"contacts": {"status": true},
"dashboard": {"status": false},
"help": {"status": true}}'::jsonb)
where (value::json)->>'status' = 'true';

key | ?column?
----------+----------
chat | true
help | true
admin | true
accounts | true
contacts | true
(5 rows)

or

select key, (value::json)->'status' from jsonb_each(
'{
"accounts": {"status": true},
"admin": {"status": true},
"calendar": {"status": false},
"chat": {"status": true},
"contacts": {"status": true},
"dashboard": {"status": false},
"help": {"status": true}}'::jsonb)
where ((value::json)->>'status')::boolean;

key | ?column?
----------+----------
chat | true
help | true
admin | true
accounts | true
contacts | true
(5 rows)

Regards
Charles

> --

> Michael
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general