Dear all 
We recently worked quite a lot with the JSON format and thought maybe some of you could also make use of the following resources we discovered for parsing JSON in Postgresql (Postgres 12):
There is a nice documentation about a lot of functions to work with JSON here but it’s really quite a wide range of different functions. For us, the most useful was jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb [, silent bool]] together with the JSONpath specification one can find in Section 8.14.6 (also with useful links to more complicated operators and methods). This one is pretty nice because it is also quite similar to other JSONpath specifications (like we use them in our workflows for example).
Example which extracts all column_names (sorry for the bad formatting, didn’t find a good code integration here):
SELECT jsonb_path_query_array(‘{ “data_parameters”: [
{
“asset_id”: “fcd26a0bf7d741dd36abf”,
“columns”: [{“column_name”: “profits”, “variable_name”: “col_1”}, {“column_name”: “costs”, “variable_name”: “col_2”}]
},
{
“asset_id”: “jksadlslahedaksdjgh”,
“columns”: [ {“column_name”: “age”, “variable_name”: “col_3”} ]
}
] }’::jsonb, ‘$.data_parameters[].columns[].column_name’) AS this