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.klaver@aklaver.com