Re: how to query against nested hstore data type

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Monday, August 25, 2014 11:42 PM
To: Huang, Suya; pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com

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

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