Thread: [GENERAL] Question about jsonb and data structures
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.
Am I missing something?
Thanks in advance,
Emilie Laffray
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 rowsin 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 builton 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 afunction 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 eithertype, 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
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 21/06/2017 01:01, Emilie Laffray wrote:Hello,Maybe you could try smth like :
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.
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
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 > >