Hello! How to write a function, for example convert_json_to_columns, which converted data from JSONB format so that you can use the output parameters as a sample of columns for the query

select (SELECT convert_json_to_columns ('[{"cmp": "id", "cmp": "name", "cmp": "ean13"}]] :: :: jsonb)) from cmp

to end up with select cmp.id, cmp.name, cmp.ean13 from cmp

  • 1. You have incorrect JSON, the structure cannot have 3 fields with the same name (cmp), try casting this string to jsonb and see that only one value is returned. 2. dynamic formation of column names in SQL is not possible in principle from any sources. Here's a function that will build the query as text and execute it, more real - Mike
  • I tried to do this, but SELECT * FROM jsonb_to_record (replace (trim (both '[]' from '[{"table": "cmp"}, {"cmp": "id"}])),}, { ',', ') :: jsonb) AS x ("table" text, "cmp" text), but it is impossible to convert cmp.id into text and insert the result into the query. Plus I think to add it so that the incoming data is processed in a cycle - mcmot
  • Any ideas how to make a function that will build a query as a text and execute it? - mcmot 2:41 pm
  • Use execute stackoverflow.com/questions/12780275/… - Mike
  • Thanks for the link helped. Another question: to use the condition converted from the JSONB array, I tried to use SELECT * FROM jsonb_to_record (replace (trim (both '[]' from '[{"table": "cmp"}, {"cmp": "id"}) ] '),'}, {',', ') :: jsonb) AS x ("table" text, "cmp" text), but it turns out a table, But how to use stackoverflow.com/questions/31713182 ... ... to get in the end cmp.id? - mcmot

0