In need of some JSONB examples ?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 the format 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general