MS SQL json查询/where子句嵌套数组项

Question

我有一些json数据，可以使用CROSS APPLY OPENJSON(进行查询，但是当您添加多个交叉应用程序或json文档变得太大时，查询速度会变慢。因此，我想在过滤的数据上添加索引，但是没有使用交叉应用程序无法使嵌套数组项的语法工作。因此，我无法创建索引，因为在创建索引时不能使用交叉应用程序。根据MS文档，我应该只需执行以下操作：

JSON_query(my_column, $.parentItem.nestedItemsArray1.nestedItemsArray2)

我应该能够获取所有嵌套的数组项的值，然后进行查询，并通过添加索引来提高性能，类似于这样

ALTER TABLE mytable 
ADD vdata AS JSON_query(my_column, 
  $.parentItem.nestedItemsArray1.nestedItemsArray2')  
  
CREATE INDEX idx_json_my_column ON mytable(vdata)

但上述的$.array.arrayitems语法不起作用吗？顺便说一下，我不禁想到关系型术语，通常在Sql中你会这样索引数据列。

col
---
 1| 
 2|
 3|

但是JSON数据似乎会被铺平，因此当我按照Microsoft示例使用JSON_QUERY时，我得到了"1,2,3"。 我假设我想要索引值的数组而不是扁平化版本，除非索引将返回扁平化数据的内部数据？

我的即插即用工作示例

declare  @mydata table (    
  ID int NOT NULL,  
  jsondata varchar(max) NOT NULL  
)

INSERT INTO @mydata (id, jsondata)
    VALUES (789, '{ "Id": "12345", "FinanceProductResults": [ { "Term": 12, "AnnualMileage": 5000, "Deposits": 0, "ProductResults": [] }, { "Term": 18, "AnnualMileage": 30000, "Deposits": 15000, "ProductResults": [] }, { "Term": 24, "AnnualMileage": 5000, "Deposits": 0, "ProductResults": [ { "Key": "HP", "Payment": 460.28 } ] }, { "Term": 24, "AnnualMileage": 10000, "Deposits": 0, "ProductResults": [ { "Key": "HP", "Payment": 500.32 } ] }]}')

SELECT 
    j_Id
    ,JSON_query (c.value, '$.Term') as Term
    ,JSON_Value (c.value, '$.AnnualMileage') as AnnualMileage
    ,JSON_Value (c.value, '$.Deposits') as Deposits     
    ,JSON_Value (p.value, '$.Key') as [Key]
    ,JSON_Value (p.value, '$.Payment') as Payment
    --,c.value
FROM @mydata  f 
    CROSS APPLY OPENJSON(f.jsondata)
        WITH (j_Id nvarchar(100) '$.Id')
    CROSS APPLY OPENJSON(f.jsondata, '$.FinanceProductResults') AS c
    CROSS APPLY OPENJSON(c.value, '$."ProductResults"') AS p
where 
    ID = 789 
    AND JSON_Value (p.value, '$.Payment') = '460.28'

我正在使用这些微软文档作为指南： 如何创建索引 如何获取数据 更新 我能够使用“with”方法稍微提高性能。

SELECT 
    j_Id,
    FinanceDetails.Term,
    FinanceDetails.AnnualMileage,
    FinanceDetails.Deposits,
    Payments.Payment
FROM @mydata  f 
    CROSS APPLY OPENJSON(f.jsondata)
        WITH (j_Id nvarchar(100) '$.Id')
    OUTER APPLY OPENJSON (f.jsondata, '$.FinanceProductResults' )  
            WITH (   
                Term            INT             '$.Term',  
                AnnualMileage   INT             '$.AnnualMileage',  
                Deposits        INT             '$.Deposits',  
                ProductResults  NVARCHAR(MAX)   '$.ProductResults'  AS JSON  
            ) AS FinanceDetails
        OUTER APPLY OPENJSON(ProductResults,  '$') 
            WITH (   
                Payment DECIMAL(19, 4)          '$.Payment' 
            ) AS Payments
WHERE   
    Payments.Payment = 460.28

但是我仍然希望在子数组数据上添加索引，以帮助提高性能？

- Rawdon Hume

Answer 1

目前，您无法对嵌套属性建立索引。 全文搜索是否是可行的选项？您可以在JSON列上创建全文搜索，并添加谓词：

WHERE ....
AND CONTAINS( jsondata, 'NEAR(('Payments,460),1)')

由于JSON是文本格式，因此该谓词将过滤掉所有没有类似“Payment”和460附近的记录（这将识别键值对），并且您可以在减少的行集上应用CROSS APPLY。

- Jovan MSFT

---

很遗憾，我曾希望由于MS SQL 2016在某种程度上支持json，它将支持对嵌套项进行索引。但至少现在我知道我没有错过什么，而且这是不可能的，谢谢 :) - Rawdon Hume