Menu
I'm using SQL Server 2016, which supports JSON PATH to return JSON string.
I wonder how to get just a simple empty json array, I mean [] when my query or sub-query returns null. I've tried this query:
SELECT '' AS TEST
FOR JSON PATH,ROOT('arr')
which returns:
{"arr":[{"test":""}]}
and also this one:
SELECT NULL AS TEST
FOR JSON PATH,ROOT('arr')
which returns:
{"arr":[{}]}
it's better but still not correct, I need this:
{"arr":[]}
452
You can use the regular length() method. It returns the size of JSONArray. If the array is empty, it will return 0. So, You can check whether it has elements or not.
// create an empty array var array = []; // create an object with properties "id", "action" with mock values var object = { id: "1", action: "shout" } // add object to array array. push(object); // create encode json string var myJSONString = JSON. stringify(array);
"JSON has a special value called null which can be set on any type of data including arrays, objects, number and boolean types." "The JSON empty concept applies for arrays and objects...Data object does not have a concept of empty lists. Hence, no action is taken on the data object for those properties."
If you have JSON text that's stored in database tables, you can read or modify values in the JSON text by using the following built-in functions: ISJSON (Transact-SQL) tests whether a string contains valid JSON. JSON_VALUE (Transact-SQL) extracts a scalar value from a JSON string.
You can always check this with ISNULL, e.g.:
select ISNULL( (SELECT * FROM sys.tables where 1=2 FOR JSON PATH), '[]')
If you need this in app layer, maybe it would be better to check is there some results set in data access code, and if not just return [] or {}.
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
