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