How to parse Key Values from hstore postgres to use keys as Labels

I am operating on Grafana 9
I have two columns i am querying from Postgres to Grafana viz; time and hstore, the hstore(hash map/dictionary) as I want to use time series graph. I am new to PSQL don’t use much.

I want to use the time column as X axis and the values of the keys from hstore as Y axis for different keys(Labels)
Basically dashboard needs to show a time series graph for each Key from the hash map. The Key is variable and can change

So far i tested it with static Key, it works fine. But since the key is variable I am not able to do it. Below is the working scenario with static keys.

What can i do to test this.


try converting the hstore to json using the function hstore_to_json like this:


And then in the section Transform, look for “Extract Fields”, select the json column and keep the format as Auto.

You can then override the json column and hide it.

1 Like

Awesome this worked thank you @clevernessisamyth
Although looks like the hstore stores the integers as strings and have to add one more transform to transform the fields. Is there a way i can avoid the seconds transform.(can hstore keep the values in intergers?)

I couldn’t find a way to dynamically change the fields type.

I tried “Status history” and it resulted like this:

Not sure if this suits your needs. Wait for some other one to tell us how can we change the types.

Or try Apache Echarts

1 Like

@dparab Nevermind, I didn’t read the full hstore to json documentation x)

Just use hstore_to_jsonb_loose instead of hstore_to_json, and everything will work fine.

@clevernessisamyth you have single handedly saved few more days of rabbit holing for me
Thanks a lot, you are amazing :v:t5:


I’m glad that worked for you. You’re welcome.

Have a good day!