如何设置 OPENJSON 路径来访问嵌套数组

Question

我正在尝试设置OPENJSON函数的嵌套数组路径，但无法成功。我尝试了不同的变体和在线资源，仍然无法解决这个问题。

有任何想法吗？

编辑：

明确一下，我知道如何使用CROSSAPPLY和其他方法来做到这一点。我的问题是关于如何在可能的情况下使用OPENJSON函数的路径参数来完成此操作。

以下是我的代码:

DECLARE @json NVARCHAR(MAX);
SET @json = '
{
    "orders": [
        {
            "id":"1",
            "date":"7/4/2020",
            "orderlines": [
                {"id": "1", "amount": 100}, 
                {"id": "2", "amount": 200}
            ]
        },
        {
            "id":"2",
            "date":"7/4/2020",
            "orderlines": [
                {"id": "3", "amount": 300}, 
                {"id": "4", "amount": 400}
            ]
        }
    ]
}
'
-- None of these return results. How do I specify the path to the "orderlines" array?
SELECT * FROM OPENJSON(@json,'$.orderlines');
SELECT * FROM OPENJSON(@json,'$.orderlines[1]');
SELECT * FROM OPENJSON(@json,'$.orders.orderlines');
SELECT * FROM OPENJSON(@json,'$.orders.orderlines[1]');

-- This works:
SELECT * FROM OPENJSON(@json,'$.orders');

- ptownbro

Answer 1

你可以使用WITH关键字并在内部值上放置一个名称，然后使用CROSS APPLY在另一个OPENJSON中使用它们。现在你可以将所有内部对象合并在一起。

SELECT orderlines.id, orderlines.amount 
FROM OPENJSON(@json, '$.orders') WITH (orderlines NVARCHAR(MAX) '$.orderlines' AS JSON) orders
CROSS APPLY OPENJSON(orders.orderlines) WITH (id INT '$.id', amount INT '$.amount') orderlines

点击这里了解更多。

如果需要获取数组中的特定项：

SELECT * FROM OPENJSON(@json, '$.orders[0].orderlines[0]')
-- OR
SELECT JSON_VALUE(@json, '$.orders[0].orderlines[0].amount')

- iman kazemi

---

正如我在发布的帖子中提到的那样，我已经知道如何使用CROSSAPPLY来实现这一点。然而，你在底部给出的建议才是答案。谢谢！ - ptownbro

Answer 2

为了让其他人更清楚，可能会查看Iman Kazemi的回复，他回答的部分是他所写的以下内容：

SELECT * FROM OPENJSON(@json, '$.orders[0].orderlines[0]')

我忘记在订单数组上指定索引。

再次感谢Iman。

- ptownbro

Answer 3

你可以尝试以下方法：

SELECT *
FROM OPENJSON (@json, '$.orders')
WITH (
    id INT '$.id',
    [date] VARCHAR(10) '$.date',
 
    orderlines_id1 INT '$.orderlines[0].id',
    orderlines_amount1 MONEY '$.orderlines[0].amount', 
 
    orderlines_id2 INT '$.orderlines[1].id',
    orderlines_amount2 MONEY '$.orderlines[1].amount'
) AS Orders

请查看 db<>fiddle 这里。

- sacse

---

我感谢您的回复，但是我正在寻找如何使用OPENJSON函数的路径参数来实现这一点。我知道其他方法可以做到这一点。我已经编辑了我的原始帖子以使其更清晰。谢谢。 - ptownbro