logo标签列表

如何在 JSON_QUERY 中有条件地返回 NULL

sqljsonsql-server
3

如何返回一个JSON_QUERY，如果子查询（或左连接）中选择的值为空，则返回NULL（或根本没有属性）？

这里有一个简单的示例（假设下面的NULL是一个子查询）：

SELECT 
    JSON_QUERY((SELECT NULL as SomeColumn 
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test

这将返回一个空的JSON对象 - 这不是我想要的：
Test
----------------
{}

由于子查询返回了 NULL，因此我希望如果子查询返回 NULL，它也能返回 NULL，否则应该返回 JSON。

即：

当子查询返回 NULL 时，JSON 结果应为：

Test
----------------
NULL

当子查询返回JSON时，JSON结果应为：

Test
----------------
{ someColumn: 'some json' ... }

如何使用JSON查询来完成这个任务？

[编辑]

我尝试了这个，认为我可以使用CASE语句：

SELECT 
    JSON_QUERY((SELECT JSON_VALUE('{"SomeColumn":"Test"}', '$') AS SomeColumn 
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test

但是这不会选择根元素。上面的代码返回：
Test
------
{}

使用案例

我需要的使用案例是这样的。

当我返回JSON查询时，如果地址存在，我希望能够返回以下内容:

   { 
      name: 'John',
      address: {
         line1: '123 elm st'
      }
   }

如果没有地址记录，我希望它返回：

   { 
      name: 'John',
      address: NULL
   }

或者

   { 
      name: 'John'
   }

我不希望它返回

   { 
      name: 'John',
      address: {}
   }
7个回答
1
如果我正确理解了问题，您可以尝试以下方法，使用从JSON_VALUE()获得的结果作为条件：
SELECT JSON_VALUE(
   (SELECT NULL as SomeColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
   '$.SomeColumn'
) AS Test

结果：

Test
----
null

但是，如果JSON输出包含来自多个列的信息，则可以将从 OPENJSON()返回的行数作为检查条件进行计数。使用默认模式从 OPENJSON()获得的结果是一个表格，其中包含来自JSON文本的每个键/值对的单行：

SELECT CASE WHEN EXISTS(SELECT 1 FROM OPENJSON(Json)) THEN Json END AS Test
FROM (VALUES
   ((SELECT NULL AS SomeColumn, NULL AS OtherColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)),
   ((SELECT NULL AS SomeColumn, 123 AS OtherColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
) j (Json)

结果：

Test
-------------------
null
{"OtherColumn":123}
我需要选择整个JSON结果，如果不为空。你的例子只选择了一个列（SomeColumn）。是否可能返回整个JSON结果？即如果它不为空，则返回NULL？ - pixelbits
@pixelbits 最后一个示例返回整个JSON。谢谢。 - Zhorov
这会返回一个字符串对象，而不是JSON结果。当我尝试测试返回JSON结果的子查询时，这将成为一个问题，因为它们将被视为字符串。 - pixelbits
@pixelbits JSON_QUERY() 返回一个 nvarchar(max) 作为结果。没有特殊的 JSON 数据类型。所有的 JSON 内容都表示为文本。但是你的用例很有趣。我仍然认为，使用 OPENJSON() 作为检查条件来检查“空”JSON（数组或对象）是一个不错的选择。但是当这个“空”的 JSON 是更复杂的 JSON 中的嵌套元素时，你需要再次解析整个 JSON。说实话，使用 REPLACE(Json, '{}', 'null') 的基于字符串的方法也是一种选择。 - Zhorov
1
像这样吗？
SELECT NULLIF(JSON_QUERY((SELECT NULL as SomeColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)), '{}') as Test
实际上，这并不起作用。这个 SELECT NULLIF(JSON_QUERY((SELECT top 1 value from string_split('1,2,3',',') FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)), '{}') as Test 返回的是一个字符串，而不是一个JSON对象。我需要JSON对象。 - pixelbits
1

是的，你可以使用 CASE..WHEN 表达式：

WITH t AS
(
  SELECT JSON_QUERY((SELECT JSON_VALUE('{"SomeColumn":"Test"}', '$') AS SomeColumn 
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test
)     
SELECT CASE WHEN Test = '{}' THEN 'NULL' ELSE Test END AS Test
  FROM t;


Test
-----
NULL

如果你想要一个空值（布尔表达式），而不是字符串“NULL”，那么请使用以下代码：CASE WHEN Test != '{}' THEN Test END AS Test。 演示
似乎使用JSON_VALUE并选择“$”未选择根，这不是我所期望的。 - pixelbits
嗨@pixelbits。首先，您到底想要获取NULL还是'NULL'作为字符串？ - Barbaros Özhan
我想要获取NULL或JSON对象。 - pixelbits
@pixelbits 如果您想获取 NULL（一个布尔表达式），那么您可以使用 WITH t AS ( SELECT JSON_QUERY((SELECT JSON_VALUE('{"SomeColumn":"Test"}', '$.SomeColumn') AS SomeColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test ) SELECT CASE WHEN Test != '{}' THEN Test END AS Test FROM t。请参考此链接。 - Barbaros Özhan
不是 @pixelbits，JSON对象是一个特殊格式的字符串。与Postgres不同，SQL Server没有数据类型JSON。看一下[这个]（https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=cb6b8ccfaa32e3b9a59baabea1a6c44a）。 - Barbaros Özhan
显示剩余2条评论
1
如果JSON_QUERY没有找到任何值，则返回NULL。当您测试JSON_QUERY=N'{}'（当没有找到任何内容时）时，它将计算为FALSE。
运行此代码并查看结果。
declare
  @json         nvarchar(max)=N'{ "objects": [ { "shape": "circle" }, {"shape": "square" } ] }',
  @nojson       nvarchar(max)=N'{}';

select json_query(@json, '$.objects[0]'); /* returns { "shape": "circle" } */
select json_query(@json, '$.objects[1]'); /* returns {"shape": "square" } */
select json_query(@json, '$.objects[3]'); /* returns NULL */

select case when json_query(@json, '$.objects[20]')=N'{}' then null else json_query(@nojson) end; 
/* returns {} */

select case when json_query(@json, '$.objects[20]') is null then 'True NULL' else 'False NULL' end;
/* returns 'True NULL' */
1
我尝试了这个方法，认为我只需要使用CASE语句即可： SELECT JSON_QUERY((SELECT JSON_VALUE('{"SomeColumn":"Test"}', '$') AS SomeColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test 但是这样无法选择根节点。
json_value()检索标量值（null/boolean/string/number）。
--"a" value is a scalar/string
select json_value('{"a":"1234"}', '$.a') --> returns 1234

--"a" value is an object
select json_value('{"a":{"anothercolumn": "the value of anothercolumn"}}', '$.a') --> returns null, since a is not a scalar value

因此，json_value('任何 JSON 内容', '$') 将始终返回 NULL，因为 $elf 是对象，而 json_value() 只能检索标量。
select json_value('{"a":"b"}', '$'), json_value('[1, 2]', '$'), json_value('[{"a":"b"}, {"c":"d"}]', '$') --all NULL

用于检索对象或数组值的方法是json_query()：

select json_query('{"a":{"anothercolumn": "the value of anothercolumn"}}', '$.a') --> returns {"anothercolumn":...}, since value of "a" is an object
--...and....
select json_query('{"a":"b"}', '$'), json_query('[1, 2]', '$'), json_query('[{"a":"b"}, {"c":"d"}]', '$') --each json

如果您想从一个子查询中检索可能返回NULL或JSON对象字符串的"对象"，请将code from @iceblade包装在json_query()中。
SELECT JSON_QUERY(
NULLIF(JSON_QUERY((SELECT NULL as SomeColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)), '{}')
) as Test;

SELECT 
     JSON_QUERY (NULLIF((SELECT v.col as SomeColumn 
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER), '{}'))  as 'jsonquery'
from (values ('a'), (null), ('b')) as v(col);

SELECT 
     JSON_QUERY (NULLIF((SELECT v.col as SomeColumn 
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER), '{}'))  as 'jsonquery'
from (values ('a'), (null), ('b')) as v(col)
for json path, INCLUDE_NULL_VALUES;
0
SELECT ISNULL(@JSONPATHQUERY),'{}')
你的回答可以通过提供更多支持信息来改进。请编辑你的回答，添加更多细节，例如引用或文档，以便其他人可以确认你的回答是正确的。你可以在帮助中心找到有关如何编写好的答案的更多信息。 - Syscall
0

我找到了一个简单的解决方案。

当左连接（或子查询）的记录不存在时，有条件地返回 NULL 属性，可以使用“点符号”属性：

SELECT
    EMP.firstName as [Employee.FirstName],
    EMP.lastName as [Employee.LastName],
    A.Line1 as [Employee.Address.Line1],
    A.City as [Employee.Address.City]
FROM
   Employee EMP
LEFT OUTER JOIN Address A ON A.Id = EMP.AddressId
FOR JSON PATH

当员工有地址时，这将返回以下内容：
{
   employee: {
       firstName: 'john',
       lastName: 'smith',
       address: {
          line1: 'elm st',
          city: 'New York'
       }
   } 
}

但是如果员工没有地址记录，查询将返回以下内容：

{
   employee: {
       firstName: 'john',
       lastName: 'smith'
   } 
}

注意：

  • 您需要确保所有点符号属性都被分组，以便属性和子属性在一起。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接