Question about jsonb and data structures

On 21/06/2017 01:01, Emilie Laffray wrote:

Hello,

I have been playing with Postgresql recently with a large table and I have started looking at reducing the number of rows in that table.
One idea to reduce the actual size, I thought I would "compress" the data structure into a JSON object (more on that later).
The table is pretty straightforward in itself
other_id integer
type_id integer
label_id integer
rank_id integer
value real

and the goal is to move to a data structure where we have
other_id integer
value jsonb

There are many things in the table that is not optimal for legacy reasons and I can't just get rid of them.

I looked at several json object data structure to see if I could make it work notably
[{"type":1,"label":1,"rank":1,"value":.5},{"type":1,"label":2,"rank":1,"value":.25}]
{"1":{"1":{"rank":1,"value":.5},"2":{"rank":1,"value":.25}}}

For better or worse, the first one would be the best for me as I can do a simple query like this using the GIN index built on top of value:
SELECT *
FROM mytable
WHERE value @> '[{"type":1,"rank":1,"label":2}]'

Effectively, I would want to extract the value corresponding to my condition using simple SQL aka not having to write a function extracting the json.

The experiment on the second data structure shows that it is not as convenient as I may need to perform search on either type, label, rank and various combinations of the fields.

Maybe you could try smth like :
test=# select * from lala;
id | txt
----+------------
1 | one
2 | two
3 | ZZZbabaZZZ
4 | ZZZbabaZZZ
5 | ZZZbabaZZZ
6 | ZZZbabaZZZ
7 | ZZZbabaZZZ
8 | ZZZbabaZZZ
9 | ZZZbabaZZZ
10 | ZZZbabaZZZ
11 | ZZZbabaZZZ
12 | ZZZbabaZZZ
13 | ZZZbabaZZZ
(13 rows)

select foo.* FROM (select id,to_jsonb(lala) as jzon from lala) as foo where jzon @> '{"id":5}';

Am I missing something?

Thanks in advance,
Emilie Laffray

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

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

Hello Achilleas,

I fail to see how it would solve my problem here. I already have a
structure that is packed and nested. Your example is on a simple key/value
pair structure and effectively you can address the ids very simply. In my
case, I would need to return only a subset of the json data.
Maybe I missed something from your example?

On Wed, Jun 21, 2017 at 12:27 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:

On Wed, Jun 21, 2017 at 09:37:20AM -0700, Emilie Laffray wrote:

Date: Wed, 21 Jun 2017 09:37:20 -0700
From: Emilie Laffray <emilie.laffray@gmail.com>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Question about jsonb and data structures

Hello Achilleas,

I fail to see how it would solve my problem here. I already have a
structure that is packed and nested. Your example is on a simple key/value
pair structure and effectively you can address the ids very simply. In my
case, I would need to return only a subset of the json data.
Maybe I missed something from your example?

Would jsquery help?

https://github.com/postgrespro/jsquery

On Wed, Jun 21, 2017 at 12:27 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:

On 21/06/2017 01:01, Emilie Laffray wrote:

Hello,

I have been playing with Postgresql recently with a large table and I
have started looking at reducing the number of rows in that table.
One idea to reduce the actual size, I thought I would "compress" the data
structure into a JSON object (more on that later).
The table is pretty straightforward in itself
other_id integer
type_id integer
label_id integer
rank_id integer
value real

and the goal is to move to a data structure where we have
other_id integer
value jsonb

There are many things in the table that is not optimal for legacy reasons
and I can't just get rid of them.

I looked at several json object data structure to see if I could make it
work notably
[{"type":1,"label":1,"rank":1,"value":.5},{"type":1,"label":
2,"rank":1,"value":.25}]
{"1":{"1":{"rank":1,"value":.5},"2":{"rank":1,"value":.25}}}

For better or worse, the first one would be the best for me as I can do a
simple query like this using the GIN index built on top of value:
SELECT *
FROM mytable
WHERE value @> '[{"type":1,"rank":1,"label":2}]'

Effectively, I would want to extract the value corresponding to my
condition using simple SQL aka not having to write a function extracting
the json.

The experiment on the second data structure shows that it is not as
convenient as I may need to perform search on either type, label, rank and
various combinations of the fields.

Maybe you could try smth like :

test=# select * from lala;
id | txt
----+------------
1 | one
2 | two
3 | ZZZbabaZZZ
4 | ZZZbabaZZZ
5 | ZZZbabaZZZ
6 | ZZZbabaZZZ
7 | ZZZbabaZZZ
8 | ZZZbabaZZZ
9 | ZZZbabaZZZ
10 | ZZZbabaZZZ
11 | ZZZbabaZZZ
12 | ZZZbabaZZZ
13 | ZZZbabaZZZ
(13 rows)

select foo.* FROM (select id,to_jsonb(lala) as jzon from lala) as foo
where jzon @> '{"id":5}';

Am I missing something?

Thanks in advance,
Emilie Laffray

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

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

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