Thread: how to query against nested hstore data type

how to query against nested hstore data type

From
"Huang, Suya"
Date:

Hi,

 

It’s my test DB, the version is PostgreSQL 9.4beta2. I’m testing with nested hstore feature.

 

testdb=# \d+ test

                         Table "public.test"

Column |  Type  | Modifiers | Storage  | Stats target | Description

--------+--------+-----------+----------+--------------+-------------

id     | text   |           | extended |              |

stats  | hstore |           | extended |              |

 

 

testdb=# select * from test;

      id       |                                                                                         stats                                                

---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2b8ea99d60b30 | "www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}", "www.strawberrynet.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"

(1 row)

 

 

testdb=# select (each(channel)).key,(each(channel)).value from visits;

          key          |                                value

-----------------------+----------------------------------------------------------------------

www.ask.com           | {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}

www.strawberrynet.com | {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}

(2 rows)

 

The question is how can I get visits/pages/duration separately from the value column so that I can do a sum on those metrics individually.

 

 

Thank you!

Suya

 

Re: how to query against nested hstore data type

From
Peter Geoghegan
Date:
On Sun, Aug 24, 2014 at 7:05 PM, Huang, Suya <Suya.Huang@au.experian.com> wrote:
> It’s my test DB, the version is PostgreSQL 9.4beta2. I’m testing with nested
> hstore feature.

Really? Nested hstore only made it into that version as jsonb, which
you're clearly not using here.


--
Regards,
Peter Geoghegan


Re: how to query against nested hstore data type

From
"Huang, Suya"
Date:
-----Original Message-----
From: Peter Geoghegan [mailto:peter.geoghegan86@gmail.com] 
Sent: Monday, August 25, 2014 12:14 PM
To: Huang, Suya
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to query against nested hstore data type

On Sun, Aug 24, 2014 at 7:05 PM, Huang, Suya <Suya.Huang@au.experian.com> wrote:
> It’s my test DB, the version is PostgreSQL 9.4beta2. I’m testing with 
> nested hstore feature.

Really? Nested hstore only made it into that version as jsonb, which you're clearly not using here.


--
Regards,
Peter Geoghegan

============================================================================================

I can insert and query rows with nested hstore structure as you can see in the output. And is able to query the key
valuepair at root level, but is not sure how to go deeper...
 



Re: how to query against nested hstore data type

From
"Huang, Suya"
Date:
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Huang, Suya
Sent: Monday, August 25, 2014 12:18 PM
To: Peter Geoghegan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to query against nested hstore data type

-----Original Message-----
From: Peter Geoghegan [mailto:peter.geoghegan86@gmail.com]
Sent: Monday, August 25, 2014 12:14 PM
To: Huang, Suya
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to query against nested hstore data type

On Sun, Aug 24, 2014 at 7:05 PM, Huang, Suya <Suya.Huang@au.experian.com> wrote:
> It’s my test DB, the version is PostgreSQL 9.4beta2. I’m testing with 
> nested hstore feature.

Really? Nested hstore only made it into that version as jsonb, which you're clearly not using here.


--
Regards,
Peter Geoghegan

============================================================================================

I can insert and query rows with nested hstore structure as you can see in the output. And is able to query the key
valuepair at root level, but is not sure how to go deeper...
 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


==============================================================================================

From this thread http://postgresql.1045698.n5.nabble.com/hstore-jsonb-td5798654.html , it says the development on
nestedhstore has frozen.
 
Also, I've found
http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=blob;f=README;h=c8af7f50cfd58ed3b1cd18f9ec34f44ada59c34e;hb=d7a5fe1d301a520c9e1f5570c4a345915865f21b
asa separate patch to be applied on 9.4 to use nested hstore.
 

Does it mean we should turn to using jsonb instead of nested hstore as it seems it's not going to be on the main
developmentbranch? 
 

Re: how to query against nested hstore data type

From
Adrian Klaver
Date:
On 08/24/2014 07:05 PM, Huang, Suya wrote:
> Hi,
>
> It’s my test DB, the version is PostgreSQL 9.4beta2. I’m testing with
> nested hstore feature.
>
> testdb=# \d+ test
>
>                           Table "public.test"
>
> Column |  Type  | Modifiers | Storage  | Stats target | Description
>
> --------+--------+-----------+----------+--------------+-------------
>
> id     | text   |           | extended |              |
>
> stats  | hstore |           | extended |              |
>
> testdb=# select * from test;
>
>        id       |
>                                                                  stats
>
>
---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 2b8ea99d60b30 |
> "www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}",
> "www.strawberrynet.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"
>
> (1 row)

So how did you get the above values in?

>
> testdb=# select (each(channel)).key,(each(channel)).value from visits;
>
>            key          |                                value
>
> -----------------------+----------------------------------------------------------------------
>
> www.ask.com           |
> {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}
>
> www.strawberrynet.com |
> {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}
>
> (2 rows)

Did you change tables in the above?

>
> The question is how can I get visits/pages/duration separately from the
> value column so that I can do a sum on those metrics individually.
>
> Thank you!
>
> Suya
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: how to query against nested hstore data type

From
"Huang, Suya"
Date:
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, August 25, 2014 11:42 PM
To: Huang, Suya; pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to query against nested hstore data type

On 08/24/2014 07:05 PM, Huang, Suya wrote:
> Hi,
>
> It's my test DB, the version is PostgreSQL 9.4beta2. I'm testing with
> nested hstore feature.
>
> testdb=# \d+ test
>
>                           Table "public.test"
>
> Column |  Type  | Modifiers | Storage  | Stats target | Description
>
> --------+--------+-----------+----------+--------------+-------------
>
> id     | text   |           | extended |              |
>
> stats  | hstore |           | extended |              |
>
> testdb=# select * from test;
>
>        id       |
>                                                                  stats
>
> ---------------+------------------------------------------------------
> ---------------+------------------------------------------------------
> ---------------+------------------------------------------------------
> ---------------+----------------------
>
> 2b8ea99d60b30 |
> "www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>
> 197980.836904}",
> "www.strawberrynet.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"
>
> (1 row)

So how did you get the above values in?

>
> testdb=# select (each(channel)).key,(each(channel)).value from visits;
>
>            key          |                                value
>
> -----------------------+----------------------------------------------
> -----------------------+------------------------
>
> www.ask.com           |
> {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}
>
> www.strawberrynet.com |
> {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}
>
> (2 rows)

Did you change tables in the above?

>
> The question is how can I get visits/pages/duration separately from
> the value column so that I can do a sum on those metrics individually.
>
> Thank you!
>
> Suya
>


--
Adrian Klaver
adrian.klaver@aklaver.com

======================================================

>>Did you change tables in the above?
Changed column name from channel to stats.


Re: how to query against nested hstore data type

From
John R Pierce
Date:
On 8/24/2014 7:05 PM, Huang, Suya wrote:

The question is how can I get visits/pages/duration separately from the value column so that I can do a sum on those metrics individually.

 


why not store all this in proper relational tables so you can use proper relational queries?



-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: how to query against nested hstore data type

From
Adrian Klaver
Date:
On 08/25/2014 06:05 PM, Huang, Suya wrote:
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Monday, August 25, 2014 11:42 PM
> To: Huang, Suya; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] how to query against nested hstore data type
>
> On 08/24/2014 07:05 PM, Huang, Suya wrote:
>> Hi,
>>
>> It's my test DB, the version is PostgreSQL 9.4beta2. I'm testing with
>> nested hstore feature.
>>
>> testdb=# \d+ test
>>
>>                            Table "public.test"
>>
>> Column |  Type  | Modifiers | Storage  | Stats target | Description
>>
>> --------+--------+-----------+----------+--------------+-------------
>>
>> id     | text   |           | extended |              |
>>
>> stats  | hstore |           | extended |              |
>>
>> testdb=# select * from test;
>>
>>         id       |
>>                                                                   stats
>>
>> ---------------+------------------------------------------------------
>> ---------------+------------------------------------------------------
>> ---------------+------------------------------------------------------
>> ---------------+----------------------
>>
>> 2b8ea99d60b30 |
>> "www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>
>> 197980.836904}",
>> "www.strawberrynet.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"
>>
>> (1 row)
>
> So how did you get the above values in?
>

The only way I could replicate what you show is:

test=> \d hstore_test
     Table "public.hstore_test"
    Column   |  Type   | Modifiers
------------+---------+-----------
  id         | integer |
  hstore_fld | hstore  |



test=> insert into hstore_test values (1, hstore('www.ask.com',
'{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}'));
INSERT 0 1


test=> select * from hstore_test ;
-[ RECORD 1
]-------------------------------------------------------------------------------------
id         | 1
hstore_fld |
"www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"


test=> select (each(hstore_fld)).key,(each(hstore_fld)).value from
hstore_test ;
-[ RECORD 1 ]---------------------------------------------------------------
key   | www.ask.com
value | {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}

If that is indeed what you did then you as far as I can tell you do not
have a nested hstore. Instead you have a key "www.ask.com" and a string
value "{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"

Where the => in the string value is decoration not functional. If you
want nesting then you will need to use the array or JSON types.


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: how to query against nested hstore data type

From
"Huang, Suya"
Date:

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Tuesday, August 26, 2014 11:58 PM
To: Huang, Suya; pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to query against nested hstore data type

On 08/25/2014 06:05 PM, Huang, Suya wrote:
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Monday, August 25, 2014 11:42 PM
> To: Huang, Suya; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] how to query against nested hstore data type
>
> On 08/24/2014 07:05 PM, Huang, Suya wrote:
>> Hi,
>>
>> It's my test DB, the version is PostgreSQL 9.4beta2. I'm testing with
>> nested hstore feature.
>>
>> testdb=# \d+ test
>>
>>                            Table "public.test"
>>
>> Column |  Type  | Modifiers | Storage  | Stats target | Description
>>
>> --------+--------+-----------+----------+--------------+-------------
>>
>> id     | text   |           | extended |              |
>>
>> stats  | hstore |           | extended |              |
>>
>> testdb=# select * from test;
>>
>>         id       |
>>
>> stats
>>
>> ---------------+-----------------------------------------------------
>> ---------------+-
>> ---------------+-----------------------------------------------------
>> ---------------+-
>> ---------------+-----------------------------------------------------
>> ---------------+-
>> ---------------+----------------------
>>
>> 2b8ea99d60b30 |
>> "www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=
>> >
>> 197980.836904}",
>> "www.strawberrynet.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"
>>
>> (1 row)
>
> So how did you get the above values in?
>

The only way I could replicate what you show is:

test=> \d hstore_test
     Table "public.hstore_test"
    Column   |  Type   | Modifiers
------------+---------+-----------
  id         | integer |
  hstore_fld | hstore  |



test=> insert into hstore_test values (1, hstore('www.ask.com',
'{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}'));
INSERT 0 1


test=> select * from hstore_test ;
-[ RECORD 1
]-------------------------------------------------------------------------------------
id         | 1
hstore_fld |
"www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"


test=> select (each(hstore_fld)).key,(each(hstore_fld)).value from
hstore_test ;
-[ RECORD 1 ]---------------------------------------------------------------
key   | www.ask.com
value | {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}

If that is indeed what you did then you as far as I can tell you do not
have a nested hstore. Instead you have a key "www.ask.com" and a string
value "{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"

Where the => in the string value is decoration not functional. If you
want nesting then you will need to use the array or JSON types.


--
Adrian Klaver
adrian.klaver@aklaver.com

==========================================================================
Thanks Adrian,

I've figured out, have to apply a separate patch to query out of nested hstore.
http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=summary 


Re: how to query against nested hstore data type

From
Adrian Klaver
Date:
On 08/26/2014 06:50 PM, Huang, Suya wrote:
>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Tuesday, August 26, 2014 11:58 PM
> To: Huang, Suya; pgsql-general@postgresql.org

>

<Quote>
I've figured out, have to apply a separate patch to query out of nested
hstore. http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=summary
<Quote>

Before you go to far with this I would read this blog:

http://obartunov.livejournal.com/177670.html

Basically the patch is a dead end.

--
Adrian Klaver
adrian.klaver@aklaver.com


Re: how to query against nested hstore data type

From
"Huang, Suya"
Date:
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, August 27, 2014 12:42 PM
To: Huang, Suya; pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to query against nested hstore data type

On 08/26/2014 06:50 PM, Huang, Suya wrote:
>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Tuesday, August 26, 2014 11:58 PM
> To: Huang, Suya; pgsql-general@postgresql.org

>

<Quote>
I've figured out, have to apply a separate patch to query out of nested hstore.
http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=summary
<Quote>

Before you go to far with this I would read this blog:

http://obartunov.livejournal.com/177670.html

Basically the patch is a dead end.

--
Adrian Klaver
adrian.klaver@aklaver.com

================================================================================================================

Hmm, may worth spending sometime on jsonb. Thanks Adrian for the notice.

Thanks,
Suya