How can I select data as JSON using a sub query with a string literal?

Question

I am trying to write a query that will transform the data into JSON and can't seem to get it right.

I have two table, Configuration & Provider. Configuration has a FK to provider.id.

This is my query:

select array_to_json(array_agg(row_to_json(t)))
 from (
 select orgid, host, port,
 concat('{Id: ', p.id, '}') as "provider"
 from configurations c
 inner join providers p on
 c.providerid = p.id
 ) t

The result looks like below; Notice the 'provider' property has the quotes around the whole value; I need it to be a valid JSON object, not a string.

 {
 "orgid": "00Do0000000aOu8ACA",
 "host": "https://somehost.com",
 "port": 5000,
 "provider": "{Id: 12345}"
 }

What I want:

 {
 "orgid": "00Do0000000aOu8ACA",
 "host": "https://somehost.com",
 "port": 5000,
 "provider": {"Id": "12345"}
 }

I feel like I'm close, but can't seem to get it. I've tried using the || operators as well, but can't get that working either. I tried searching for an answer to this, but it's kind of a hard question to convey without a sample. Hopefully this make sense.

Answer 1

You could just cast it to json, putting in the obviously missing quotes

concat('{"Id": ', p.id, '}')::json as "provider"

But it's better to use a proper function that does this

json_build_object('Id', p.id) as "provider"

db<>fiddle