Re: In need of some JSONB examples ?

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(dot)klaver(at)aklaver(dot)com
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com