postgresql 11 select value from jsonb data by path from other table field

Question

I have jsonb field 'data' in my table. I can get value of subKey by select data#>'{key1,subKey}' from table.How to insert a path into select if the path is stored in another table as a string 'key1,subKey'?

Answer 1

You should really store it as an array, not as a string, since that is how it will be used. You can just split it to an array dynamically, but then what if you ever need the comma to appear literally in the path?

with t as (select '{"key1":{"subKey":"foo"}}'::jsonb as data), 
     k as (select 'key1,subKey' as k)
select data#>regexp_split_to_array(k,',') from t,k;