Thread: In need of some JSONB examples ?
Hi,
I've tried RTFMing on the wonderful new 9.4 jsonb features, but
there's a little bit of a lack of examples as to how to do stuff.
I've got a document loaded in to a jsonb column that looks something like :
[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]
Anyway, there are a few thousands elements in that JSON array and I've
tried all sorts of combinations, but I simply can't manage to figure
out how to :
(a) Search by ID
(b) Do the equivalent of select * to list all IDs and Locations (one
of my end goals being the ability to do a "select into" from the JSON
into a standard database table)
On a completely unrelated note, I don't suppose Postgresql has any
built-in functionality to convert the output from an SQL query into
JSON ?
Tim
On Fri, Jan 23, 2015 at 8:00 AM, Tim Smith
<randomdev4+postgres@gmail.com> wrote:
> Hi,
>
> I've tried RTFMing on the wonderful new 9.4 jsonb features, but
> there's a little bit of a lack of examples as to how to do stuff.
>
> I've got a document loaded in to a jsonb column that looks something like :
>
> [{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]
>
> Anyway, there are a few thousands elements in that JSON array and I've
> tried all sorts of combinations, but I simply can't manage to figure
> out how to :
>
> (a) Search by ID
see the documentation pertaining to 'jsonb indexing', to wit:
-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company":
"Magnafone"}';
> (b) Do the equivalent of select * to list all IDs and Locations (one
> of my end goals being the ability to do a "select into" from the JSON
> into a standard database table)
>
> On a completely unrelated note, I don't suppose Postgresql has any
> built-in functionality to convert the output from an SQL query into
> JSON ?
yes. look for documentation on to_json, json_agg, json_build_object, etc.
merlin
re: (a)
>see the documentation pertaining to 'jsonb indexing', to wit:
>
>-- Find documents in which the key "company" has value "Magnafone"
>SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company":
> "Magnafone"}';
Nope, sorry, tried that. Doesn't work for me. Hence the question. ;-)
On 23 January 2015 at 15:08, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Jan 23, 2015 at 8:00 AM, Tim Smith
> <randomdev4+postgres@gmail.com> wrote:
>> Hi,
>>
>> I've tried RTFMing on the wonderful new 9.4 jsonb features, but
>> there's a little bit of a lack of examples as to how to do stuff.
>>
>> I've got a document loaded in to a jsonb column that looks something like :
>>
>> [{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]
>>
>> Anyway, there are a few thousands elements in that JSON array and I've
>> tried all sorts of combinations, but I simply can't manage to figure
>> out how to :
>>
>> (a) Search by ID
>
> see the documentation pertaining to 'jsonb indexing', to wit:
>
> -- Find documents in which the key "company" has value "Magnafone"
> SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company":
> "Magnafone"}';
>
>> (b) Do the equivalent of select * to list all IDs and Locations (one
>> of my end goals being the ability to do a "select into" from the JSON
>> into a standard database table)
>>
>> On a completely unrelated note, I don't suppose Postgresql has any
>> built-in functionality to convert the output from an SQL query into
>> JSON ?
>
> yes. look for documentation on to_json, json_agg, json_build_object, etc.
>
> merlin
On 01/23/2015 07:40 AM, Tim Smith wrote:
> re: (a)
>
>> see the documentation pertaining to 'jsonb indexing', to wit:
>>
>> -- Find documents in which the key "company" has value "Magnafone"
>> SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company":
>> "Magnafone"}';
>
> Nope, sorry, tried that. Doesn't work for me. Hence the question. ;-)
How does it not work?
In other words what was the query you tried and what was the output?
--
Adrian Klaver
adrian.klaver@aklaver.com
On Jan 23, 2015, at 7:40 AM, Tim Smith <randomdev4+postgres@gmail.com> wrote:
> re: (a)
>
>> see the documentation pertaining to 'jsonb indexing', to wit:
>>
>> -- Find documents in which the key "company" has value "Magnafone"
>> SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company":
>> "Magnafone"}';
>
> Nope, sorry, tried that. Doesn't work for me. Hence the question. ;-)
The problem is that @> only operates at the top level of the JSON object presented to it:
xof=# TABLE j;
f
--------------------
[{"a": 1, "b": 2}]
{"a": 1, "b": 2}
(2 rows)
xof=# SELECT * FROM j WHERE f @> $$ { "a": 1 } $$::jsonb;;
f
------------------
{"a": 1, "b": 2}
(1 row)
I'm actually not seeing a great solution to your particular problem. If you know for sure that everything always has
theformat you describe, you can use jsonb_array_elements to extract the individual members of the array, and use @> on
them,via a JOIN, but it's not clear that an index will help you there.
> 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)
On 23 January 2015 at 15:50, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 01/23/2015 07:40 AM, Tim Smith wrote:
>>
>> re: (a)
>>
>>> see the documentation pertaining to 'jsonb indexing', to wit:
>>>
>>> -- Find documents in which the key "company" has value "Magnafone"
>>> SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company":
>>> "Magnafone"}';
>>
>>
>> Nope, sorry, tried that. Doesn't work for me. Hence the question. ;-)
>
>
> How does it not work?
> In other words what was the query you tried and what was the output?
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
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
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.klaver@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.klaver@aklaver.com
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
On Jan 23, 2015, at 12:20 PM, Tim Smith <randomdev4+postgres@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 ? ;-) "Doesn't meet my particular use-case exactly" is not quite the same thing. -- -- Christophe Pettus xof@thebuild.com
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+postgres@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.klaver@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.klaver@aklaver.com
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@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
> "Doesn't meet my particular use-case exactly" is not quite the same thing. I would have thought my outlined use-case was pretty basic and common ? On 23 January 2015 at 20:44, Christophe Pettus <xof@thebuild.com> wrote: > > On Jan 23, 2015, at 12:20 PM, Tim Smith <randomdev4+postgres@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 ? ;-) > > "Doesn't meet my particular use-case exactly" is not quite the same thing. > > -- > -- Christophe Pettus > xof@thebuild.com >
On Sun, Jan 25, 2015 at 6:50 AM, Tim Smith
<randomdev4+postgres@gmail.com> wrote:
>> "Doesn't meet my particular use-case exactly" is not quite the same thing.
>
>
> I would have thought my outlined use-case was pretty basic and common ?
It is. If your objects are always laid out in about the same way, you
can use operator extraction for that:
postgres=# select
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb->1->'ID';
?column?
──────────
"2"
If you need to search in a more flexible way, then you need to look at
the jsquery extension; jsquery allows for arbitrary indexed
subdocument searching. see: https://github.com/akorotkov/jsquery
<compiling/installing>
postgres=# create extension jsquery;
CREATE EXTENSION
postgres=# select
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
@@ '*.ID = "2"';
?column?
──────────
t
(1 row)
Time: 0.480 ms
postgres=# select
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
@@ '*.ID = "3"';
?column?
──────────
f
(1 row)
postgres=# create table foo as select
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
as v;
SELECT 1
postgres=# create index on foo using gin (v jsonb_value_path_ops);
CREATE INDEX
postgres=# set enable_seqscan to false;
SET
Time: 0.676 ms
postgres=# explain select * from foo where v @@ '*.ID = "3"';
QUERY PLAN
─────────────────────────────────────────────────────────────────────────
Bitmap Heap Scan on foo (cost=76.00..80.01 rows=1 width=32)
Recheck Cond: (v @@ '*."ID" = "3"'::jsquery)
-> Bitmap Index Scan on foo_v_idx (cost=0.00..76.00 rows=1 width=0)
Index Cond: (v @@ '*."ID" = "3"'::jsquery)
(4 rows)
merlin
Thanks for the extra feedback Merlin. I'll look into it a bit more,
JSONB obviously needs a bit of experimentation in the lab to get my
query syntax right !
On 27 January 2015 at 00:13, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Sun, Jan 25, 2015 at 6:50 AM, Tim Smith
> <randomdev4+postgres@gmail.com> wrote:
>>> "Doesn't meet my particular use-case exactly" is not quite the same thing.
>>
>>
>> I would have thought my outlined use-case was pretty basic and common ?
>
> It is. If your objects are always laid out in about the same way, you
> can use operator extraction for that:
>
> postgres=# select
> '[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb->1->'ID';
> ?column?
> ──────────
> "2"
>
> If you need to search in a more flexible way, then you need to look at
> the jsquery extension; jsquery allows for arbitrary indexed
> subdocument searching. see: https://github.com/akorotkov/jsquery
>
> <compiling/installing>
> postgres=# create extension jsquery;
> CREATE EXTENSION
>
> postgres=# select
> '[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
> @@ '*.ID = "2"';
> ?column?
> ──────────
> t
> (1 row)
>
> Time: 0.480 ms
> postgres=# select
> '[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
> @@ '*.ID = "3"';
> ?column?
> ──────────
> f
> (1 row)
>
> postgres=# create table foo as select
> '[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
> as v;
> SELECT 1
>
> postgres=# create index on foo using gin (v jsonb_value_path_ops);
> CREATE INDEX
>
> postgres=# set enable_seqscan to false;
> SET
> Time: 0.676 ms
> postgres=# explain select * from foo where v @@ '*.ID = "3"';
> QUERY PLAN
> ─────────────────────────────────────────────────────────────────────────
> Bitmap Heap Scan on foo (cost=76.00..80.01 rows=1 width=32)
> Recheck Cond: (v @@ '*."ID" = "3"'::jsquery)
> -> Bitmap Index Scan on foo_v_idx (cost=0.00..76.00 rows=1 width=0)
> Index Cond: (v @@ '*."ID" = "3"'::jsquery)
> (4 rows)
>
> merlin