From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Question about jsonb and data structures |
Date: | 2017-06-21 07:27:02 |
Message-ID: | 7c8f9f85-9790-cd76-906e-08b5a7116dba@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sari Thiele | 2017-06-21 08:16:56 | Re: Missing folder rhel-6Workstation-x86_64 for 9.6 repo (redhat) |
Previous Message | Emilie Laffray | 2017-06-20 22:01:50 | Question about jsonb and data structures |