Extract data from JSONB

On Mon, Aug 8, 2016 at 12:08 PM, Alex Magnum <magnum11200@gmail.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)
--
Michael

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

Hello

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Paquier
Sent: Montag, 8. August 2016 05:24
To: Alex Magnum <magnum11200@gmail.com>
Cc: Postgres General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Extract data from JSONB

On Mon, Aug 8, 2016 at 12:08 PM, Alex Magnum <magnum11200@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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