Securely Formatting JSONB Column Names

[akumareog]

Is there a way to take advantage of the format function for dynamically generating the correct SQL for jsonb columns in the WHERE clause? I know for non-JSON columns you can do:

format('${columnName:name}', { columnName: 'id'})
// OUTPUT: '"id"'

Is there a way to do this for nested fields in a jsonb column where the output would be '"json_column_id" #>>'{key1,key2}''?
I'm able to get my desired output via a "hacky" method, but I'm not entirely sure this is secure:

format('${column:name}#>>${pathArray:csv}', {
  column: namePath.slice(0, 1),
  pathArray: `{${namePath.slice(1).join(',')}}`,
});

[vitaly-t]

There is no special provision for jsonb in this library.

A little modified code than what you are using:

format('${column.0:name}#>>${pathArray}', {
  column: namePath,
  pathArray: `{${namePath.slice(1).join(',')}}`,
});

i.e. you can use nested variable names, including indexes, and there's no point using :csv filter when you pass in just one pre-formatted string.

[akumareog]

Would SQL injection be a concern here?

[vitaly-t]

Not likely. One would have to pass in column names directly from a website for that. If you are not doing that, then you're safe.

[akumareog]

Good to hear. Think I left the :csv in as a relic of some testing but many thanks!