| From: | Emilie Laffray <emilie(dot)laffray(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Question about jsonb and data structures |
| Date: | 2017-06-20 22:01:50 |
| Message-ID: | CA+Zmw30dA=eGCqF0eXoVVm2ye3uqvKR7D1oM5Q+3E_RBdS8TOw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Achilleas Mantzios | 2017-06-21 07:27:02 | Re: Question about jsonb and data structures |
| Previous Message | Adrian Klaver | 2017-06-20 20:47:22 | Re: Schedule |