Re: Extract data from JSONB

["Charles Clavadetscher" <clavadetscher@xxxxxxxxxxxx>]

Hello

> -----Original Message-----
> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Michael Paquier
> Sent: Montag, 8. August 2016 05:24
> To: Alex Magnum <magnum11200@xxxxxxxxx>
> Cc: Postgres General <pgsql-general@xxxxxxxxxxxxxx>
> Subject: Re:  Extract data from JSONB
> 
> On Mon, Aug 8, 2016 at 12:08 PM, Alex Magnum <magnum11200@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



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