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?

On Wed, Jun 21, 2017 at 12:27 AM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>