Array to String in CosmosDB using sql query

Question

Hi,

It is possible to convert “service_code” array to a normal comma-separated string using cosmos dB SQL API query?

Select c.tin, c.service_code from c

[
{"tin": "111-121212","service_code": [ “01",03"]},
{"tin": "222-121212","service_code": [ “01",03"]}
{"tin": "3333-121212","service_code": [ “01",03"]}

]

Expected output

[
{"tin": "111-121212","service_code": “01,03”},
{"tin": "222-121212","service_code": “01,03”},
{"tin": "3333-121212","service_code": “01,03”}
]

Answer 1

ToString() function can handle this.

SELECT ToString(c.service_code) from c

If you like to change delimeter, you can create your own UDF function to handle it too.

Answer 2

Easiest way to create one will be from the portal.
In the following example, I gave Array_ToString name to this function.
All it does is, it takes the input and returns it as string.

In the following picture, you can see how to run and see its results.