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