Dynamically read JSON fields from PostgreSQL

Hi everyone,
I am using timescaleDB (PostgreSQL) for my timeseries data. My table i want to query has a TIMESTAMPZ field named ‘time’ and a JSON field named ‘data’ where are stored some sensor values which are too dynamic to be stored in different fixed columns.
I would like the sensor data to be dynamically displayed as individual lines and such as I do not have to write (data ->> 'my_field_1')::NUMERIC for each and every possible field.
Is there a way to “unnest”, or reinterpret the JSON results after the query just like the mongoDB datasource does?

Welcome to forum @lpnc

Try the extract Transformation or Check to see if one of the function here might help you to do it on the datasource itself.

What does the json column data look like?

Thank you @yosiasz !
My JSON documents are always flat documents filled with floats such as :

{
    sensor_qvd: 234.23,
    sensor_evl: 322.293,
    sensor_fdo: 28.892,
    ...
}

that is not proper json data but here are a couple of approaches

using grafana extract transformation

using json functions (in this case ms sql function, you will have to do your thing for postgres using it’s json functions)

1 Like

Thank you,
I was able to parse my JSON document using the extract field transform and to remove the JSON field with filter by name transform.

1 Like