We would like to build JSON strings of this format using SQL functions: {"foo":"{\"bar\":\"baz\"}"}.

We know that json_object('foo', json_object('bar', 'baz')) cannot work because of the JSON subtype causing the inner object to nest instead of being encoded twice (it generates {"foo":{"bar":"baz"}}, as it should). Is there a way to explicitly disable that? We've seen that CAST(json_object(..) as TEXT) is not enough to clear the subtype - using '' || json_object(..) works, but we're wondering if that's the canonical way or if there's something more efficient.

For context, we have a trigger defined on tables that is designed to collect writes as a JSON object (something like INSERT INTO local_writes (data) VALUES (json_object('column_1', NEW.column1, ...))). When writing JSON into a text column, we still want to report that as TEXT - and this breaks for things like UPDATE original_table SET column_1 = json(...), because the resulting row in local_writes now contains a nested object instead of text.

Thanks - I agree it's very unusual, but we want to capture inserted rows as they would appear in the database - so it's just the text without interpreting it as nested JSON. The concat trick should work for us.