如何将JSON对象集合解析为SQL表并遍历所有节点？

Your current query is hardcoded to only fetch item1, which is why you can't iterate over all items. The source JSON is an object with top-level keys (item1, item2, item3), each containing the nested fields you need. Here's how to adjust your SQL to get all items and their inner fields:

Corrected SQL Query #

DECLARE @json NVARCHAR(MAX) = '{ "item1": { "title": "2", "value": null, "visible": true, "name": "item1", "enabled": true, "readonly": false, "id": "f1f46ce6-9d0b-4eaf-88b7-d35b23a4d2e4" }, "item2": { "title": null, "value": null, "visible": true, "name": "item2", "enabled": true, "readonly": false, "id": "da2b8a02-cfbd-4de8-8a33-74e2a484475a" }, "item3": { "title": "", "value": null, "visible": true, "name": "item3", "enabled": true, "readonly": false, "id": "57ee45d6-41d7-45c2-b022-13220e31d2d2" } }';

SELECT 
    j.[key] AS ItemName,
    i.Title,
    i.Value,
    i.Visible,
    i.Name,
    i.Enabled,
    i.ReadOnly,
    i.Id
FROM OPENJSON(@json) j
CROSS APPLY OPENJSON(j.value) WITH (
    Title VARCHAR(100) '$.title',
    Value VARCHAR(100) '$.value',
    Visible BIT '$.visible', -- Use BIT for boolean values instead of VARCHAR
    Name VARCHAR(100) '$.name',
    Enabled BIT '$.enabled',
    ReadOnly BIT '$.readonly',
    Id VARCHAR(500) '$.id'
) i;

How It Works #

1. OPENJSON(@json): This parses the root JSON object and returns each top-level key-value pair as a row. The [key] column gives us the item name (item1, item2, item3).
2. CROSS APPLY OPENJSON(j.value): For each row from the root parse, we take the value (the nested object for each item) and parse it into structured columns using the WITH clause. This maps each nested field to a SQL column with the appropriate data type.
3. Data Type Optimization: Using BIT for boolean fields (visible, enabled, readonly) is more efficient and accurate than storing them as VARCHAR.

Sample Output #

This matches the tabular format you're aiming for, with all items and their inner fields included.

内容的提问来源于stack exchange，提问作者0x7E1