Parameterization for deeply nested jsonb

[sergae]

I want to be able to get an object from within a deeply nested jsonb and I want to use a parameterized query to do so. Below is an example of what approaches work and which do not.

foo(bar) {
	return database.query({
		// text: `select data#> '{path,$1}' from table`, // approach 1: does not work -- error: bind message supplies 1 parameters, but prepared statement "" requires 0
		// text: `select data#> '{path,'$1'}' from table`, // approach 2: does not work -- syntax error near "$1"
		// text: `select jsonb_extract_path(data,'path',$1) from table`, // approach 3: this one works
		values: [bar]
	})
}

current:
{
	"data": {
		"path": {
			"baz": {}
		}
	}
}

expected:
{
	"data": {
		"path": {
			"bar": "bonk"
		}
	}
}```

In the case where instead of getting some data I'm setting the data instead, approach 3 will not work since I will have to convert whatever I'm passing in into a jsonb and will find myself in the situation of approaches 1 and 2. Here's an example query:

const bonk = "bonk"
foo(bonk) {
return database.query({
text: UPDATE table SET data = jsonb_set(data,'{path,anotherPath}',to_jsonb('{"bar":$1}'::json)),
values: [bonk]
})
}

So the question: Is there a way to use parameters to set a part of a deeply nested jsonb? 

[charmander]

to_jsonb('{"bar":$1}'::json)

You seem to be looking for jsonb_build_object. If the parameter can be any JSON, specify the jsonb type and pass a JSON string as the value:

return database.query({
    text: `UPDATE table SET data = jsonb_set(data, '{path,anotherPath}', jsonb_build_object('bar', $1::jsonb))`,
    values: [JSON.stringify(bonk)],
})

If it can only be a string, specify the text type and pass the value as simple text:

return database.query({
    text: `UPDATE table SET data = jsonb_set(data, '{path,anotherPath}', jsonb_build_object('bar', $1::text))`,
    values: [bonk],
})