select User.*, COALESCE((select JSON_AGG(Post.*) from Post where userId = User.userId), '[]') as postJson
Returns a “postJson” field that is a string when JSON_AGG(Post.*) returns null (when there are no records in Post that match userId = User.userId).
But we want to have a default value of empty json array when postJson is null. This is what we tried to do using the COALESCE(…, ‘[]’), but it returns a string.
Tried also COALESCE(..., JSON_SET_JSON('[]', null, '[]')).
Sorry to hear you are experiencing this difficulty. We’re happy to help!
Can you provide a few more details?
What version are you running?
Are using the managed or self-hosted service?
So you want to have [] and not NULL on empty arrays? I am wondering, if you could use case... when... . I would be happy to dig more into this and explore in parallel if you give me the schema of your table and also some sample documents that I can ingest into that table repro easily on my end.
Also a colleague told me at a glance that you should caste every value inside the coalesce to json (with :> json , or else cast the result of the coalesce to json).
Welcome to our community forums!