create table json_data(row_id int, json_text jsonb);
insert into json_data(1,
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]');
To search for an ID
select row_id, parsed.* from json_data, lateral
jsonb_to_recordset(json_data.json_text) as parsed("ID" text, location_name
text) where parsed."ID" = '1';
To get all records just drop the where clause.
Obviously you could use the result to insert the data into a table as well
if you wished.
As to results to json
select row_to_json(row_data) from (select id, parsed.* from json_data,
lateral jsonb_to_recordset(json_data.json_text) as parsed("ID" text,
location_name text)) row_data;
While the number of examples are weak - the docs are not weak in terms of
getting you in the ballpark.
John
On Fri, Jan 23, 2015 at 12:20 PM, Tim Smith <randomdev4+postg...@gmail.com>
wrote:
> So basically we're saying JSON in 9.4 is still a little way from where
> it needs to be in terms of real-world functionality ? Or am I being
> too harsh ? ;-)
>
> On 23 January 2015 at 18:49, Adrian Klaver <adrian.kla...@aklaver.com>
> wrote:
> > 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.kla...@aklaver.com
> >>
> >>
> >>
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
