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@xxxxxxxxx> > To: Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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@xxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
