Обсуждение: [GENERAL] Question about jsonb and data structures

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

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