Postgres json stock data to Grafana Candlestick

Question

I am trying to visualize stock prices with time, open, high, low, close, volume data in json, from a postgres cell using Candlestick in Grafana. In the postgres table timeseries I have two columns: ticker which is just the stock short name and jsondata which is formatted like this:

{
  "c": [
    217.68,
    221.03,
    219.89
  ],
  "h": [
    222.49,
    221.5,
    220.94
  ],
  "l": [
    217.19,
    217.1402,
    218.83
  ],
  "o": [
    221.03,
    218.55,
    220
  ],
  "s": "ok",
  "t": [
    1569297600,
    1569384000,
    1569470400
  ],
  "v": [
    33463820,
    24018876,
    20730608
  ]
}

In Grafana selecting the Candlestick visualization I am query this from the postgres server:

SELECT
  now() as time,
  ticker,
  jsondata->>'t' as t,
  jsondata->>'o' as o,
  jsondata->>'h' as h,
  jsondata->>'l' as l,
  jsondata->>'c' as c
FROM
  timeseries
WHERE
  ticker = 'a'

So I got arrays in each cell like this: IMAGE But I am not able to get the arrays into rows or make it appear in the chart. Is there any solution for this? Thanks!

The data source is this: https://finnhub.io/docs/api/stock-candles

Answer 1

We can use seperate queries joined with UNION ALL

  select
  now() as time,
  ticker,
  't' as json_col,
  jsondata->>'t' as json_value
  from   timeseries
  union all
  select
    now() as time,
  ticker,
  'o',
  jsondata->>'o' 
  from   timeseries
  union all
  select
    now() as time,
  ticker,
  'h',
  jsondata->>'h' 
  from   timeseries
  union all
  select
    now() as time,
  ticker,
  'l',
  jsondata->>'l' 
  from   timeseries
  union all
  select
    now() as time,
  ticker,
  'c',
  jsondata->>'c' 
  from   timeseries
 

time                          | ticker | json_col | json_value                          
:---------------------------- | :----- | :------- | :-----------------------------------
2022-04-12 10:43:28.011766+01 | a      | t        | [1569297600, 1569384000, 1569470400]
2022-04-12 10:43:28.011766+01 | a      | o        | [221.03, 218.55, 220]               
2022-04-12 10:43:28.011766+01 | a      | h        | [222.49, 221.5, 220.94]             
2022-04-12 10:43:28.011766+01 | a      | l        | [217.19, 217.1402, 218.83]          
2022-04-12 10:43:28.011766+01 | a      | c        | [217.68, 221.03, 219.89]            

db<>fiddle here