使用SQL提取JSON

xdnvmnnf 于 2023-10-21 发布在其他

关注(0)|答案(2)|浏览(107)

我尝试使用SQL提取以下JSON消息：

{
    "document": {
        "Invoice": {
            "_NavRecordId": "Purch. Inv. Header: 06IF+230033",
            "InvoiceNumber": "06IF+230033",
            "PurchaseOrderNumber": "P0032259",
            "InvoiceLine": [
                {
                    "_NavRecordId": "Purch. Inv. Line: 06IF+230033,1",
                    "item": "A05284",
                    "Quantity": "2",
                    "PurchaseOrderLineNumber": "1"
                },
                {
                    "_NavRecordId": "Purch. Inv. Line: 06IF+230033,2",
                    "item": "A00019",
                    "Quantity": "10",
                    "PurchaseOrderLineNumber": "2"
                }
            ]
        }
    }
}

不幸的是，我无法将这些数据放到一个表中，并将每一条数据线作为它自己的记录。
我已经试过下面的代码：

DECLARE @json NVARCHAR(MAX) = '{
    "document": {
        "Invoice": {
            "_NavRecordId": "Purch. Inv. Header: 06IF+230033",
            "InvoiceNumber": "06IF+230033",
            "PurchaseOrderNumber": "P0032259",
            "InvoiceLine": [
                {
                    "_NavRecordId": "Purch. Inv. Line: 06IF+230033,1",
                    "item": "A05284",
                    "Quantity": "2",
                    "PurchaseOrderLineNumber": "1"
                },
                {
                    "_NavRecordId": "Purch. Inv. Line: 06IF+230033,2",
                    "item": "A00019",
                    "Quantity": "10",
                    "PurchaseOrderLineNumber": "2"
                }
            ]
        }
    }
}';

SELECT
    JSON_VALUE(@json, '$.document.Invoice._NavRecordId') AS NavRecordId,
    JSON_VALUE(@json, '$.document.Invoice.InvoiceNumber') AS InvoiceNumber,
    JSON_VALUE(@json, '$.document.Invoice.PurchaseOrderNumber') AS PurchaseOrderNumber,
    JSON_VALUE(@json, '$.document.Invoice.InvoiceLine.item') AS Item,
    JSON_VALUE(@json, '$.document.Invoice.InvoiceLine.Quantity') AS Quantity,
    JSON_VALUE(@json, '$.document.Invoice.InvoiceLine.PurchaseOrderLineNumber') AS PurchaseOrderLineNumber
FROM
    OPENJSON(@json, '$.document.Invoice.InvoiceLine') 
    WITH (
        _NavRecordId nvarchar(100) '$._NavRecordId',
        item nvarchar(100),
        Quantity int,
        PurchaseOrderLineNumber int
    ) AS il;

不幸的是，我无法填写项目，数量和采购订单行号与尊重的数据

JSON

来源：https://stackoverflow.com/questions/77316954/extract-json-using-sql

2条答案

按热度按时间

xeufq47z1#

你需要这样的东西：

SELECT JSON_VALUE(@json, '$.document.Invoice._NavRecordId') AS NavRecordId,
       JSON_VALUE(@json, '$.document.Invoice.InvoiceNumber') AS InvoiceNumber,
       JSON_VALUE(@json, '$.document.Invoice.PurchaseOrderNumber') AS PurchaseOrderNumber,
       il.* -- this includes item, quantity ...
FROM
    OPENJSON(@json, '$.document.Invoice.InvoiceLine') 
    WITH (
        _NavRecordId nvarchar(200) '$._NavRecordId',
        item nvarchar(100),
        Quantity int,
        PurchaseOrderLineNumber int
    ) AS il;

Demo here

赞(0）回复(0）举报 2023-10-21

rnmwe5a22#

试试这个- SQL Server只需将JSON放入变量JSON 1。DEVICE@json1 NVarChar（2048）

SELECT
    JSON_VALUE(@json1, '$.document.Invoice._NavRecordId') AS NavRecordId,
    JSON_VALUE(@json1, '$.document.Invoice.InvoiceNumber') AS InvoiceNumber,
    JSON_VALUE(@json1, '$.document.Invoice.PurchaseOrderNumber') AS PurchaseOrderNumber,
    il._NavRecordId AS [_NavRecordId],
    il.item AS Item,
    il.Quantity,
    il.PurchaseOrderLineNumber
FROM OPENJSON(@json1, '$.document.Invoice.InvoiceLine') 
WITH (
    _NavRecordId nvarchar(100),
    item nvarchar(100) ,
    Quantity int ,
    PurchaseOrderLineNumber int 
) AS il;

结果

赞(0）回复(0）举报 2023-10-21

我来回答

使用SQL提取JSON

2条答案

相关问题

热门标签

最新问答