Re: In need of some JSONB examples ? - Mailing list pgsql-general

On 01/23/2015 10:15 AM, Tim Smith wrote:
>> How does it not work?
>> In other words what was the query you tried and what was the output?
>
> As in, it doesn't work.   Full stop....
>
> \d+ json_test
>                        Table "public.json_test"
>   Column  | Type  | Modifiers | Storage  | Stats target | Description
> ---------+-------+-----------+----------+--------------+-------------
>   content | jsonb | not null  | extended |              |
> Indexes:
>      "idxgin" gin (content)
>
>
>   truncate json_test;
> TRUNCATE TABLE
>   insert into json_test(content)  values('[{"ID": "3119","Desc":"bob"}]');
> INSERT 0 1
>
> select content->'Desc' from json_test where content @> '{"ID":"3119"}';
>   ?column?
> ----------
> (0 rows)
>

WITH c AS
     (SELECT
         jsonb_array_elements(content) AS content
     FROM
     json_test)
SELECT
     content->'Desc'
FROM
     c
WHERE
     content @> '{"ID":"3119"}'

  ?column?
----------
  "bob"
(1 row)


With the caveats that Christophe Pettus  mentioned.

>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com