Postgres Pro
Downloads
Home   >   mailing lists

Thread: Extract data from JSONB

Extract data from JSONB

From
Alex Magnum
Date:
Hi,
I need some help with extracting data from json.

I have the following jsonb field

modules  
{ 
   "accounts":     {"status": true}, 
   "admin":        {"status": true}, 
   "calendar":     {"status": false}, 
   "chat":         {"status": true}, 
   "contacts":     {"status": true}, 
   "dashboard":    {"status": false}, 
   "help":         {"status": true}
}   

How can I convert that into one row each based on status; for example if I only want to have the active modules.

Only true
   
module    | status
----------+-------
accounts  | true
admin     | true
contacts  | true
help      | true

Thanks for any advise

Re: Extract data from JSONB

From
Michael Paquier
Date:
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

Re: Extract data from JSONB

From
"Charles Clavadetscher"
Date:
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