Re: [GENERAL] Question about jsonb and data structures
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.laff...@gmail.com>
> To: Achilleas Mantzios <ach...@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 <
> ach...@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
> >
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
On Wed, Jun 21, 2017 at 12:27 AM, Achilleas Mantzios <
ach...@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
>
Re: [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 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
