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

[dparab]

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.

[clevernessisamyth]

Hello,

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

SELECT
  id,
  hstore_to_json(test_hstore)
FROM
  data

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.

[dparab]

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?)

[clevernessisamyth]

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.

[clevernessisamyth]

Or try Apache Echarts

Apache ECharts Panel 4.5.0 supports Grafana 10 | Volkov Labs

[clevernessisamyth]

@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.

[dparab]

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

[clevernessisamyth]

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

Have a good day!