如何将JSON文件中maski数组的元素导入为新表TMaski的行？

需求：将指定结构JSON文件里的maski数组内的每个元素作为单独行，导入到新表TMaski中。以下是完整实现步骤：

目标JSON结构 #

{
    "naglowek": {
        "dataGenerowaniaDanych": "20240123",
        "liczbaTransformacji": "5000",
        "schemat": "RRRRMMDDNNNNNNNNNNBBBBBBBBBBBBBBBBBBBBBBBBBB lub RRRRMMDDNNNNNNNNNN, gdzie R to cyfra roku, M – miesiąca, D - dnia daty generowania pliku, N to cyfra NIPu, a B to cyfra rachunku bankowego"
    },
    "skrotyPodatnikowCzynnych": [
        "000002e2f59c240729d0f1c460ac7b58da598792a3ea0c9052a198551fae7f1f976056964a0f4ea2722cfe5149a27a579256688692d9e003157d046daf6cc790",
        "000008a1b847d4fe26fb9ade8a836fb4922d936da15b52dbe22b3192d3dae7aef9b9ace2c59001de8ddd172f51b82eadc47c68a2714d0ad3cabb99ae8e563c1d",
        "00000957b00aca4a0eb09c34040690448337f24999fbe958c287bce4c749e46026fc6e735e9cda3f62ca4e60b4b26c5c9d6e4eca38fcdd2ba2ac2330964458ff"
    ],
    "skrotyPodatnikowZwolnionych": [
        "fffc45a348278154d31e8cae404c4cb3c7e59e4fe0db96a4dc8592c382be7b41d1e7bbd83fad47468431d0b6a5bab51bbeec3e9704045c41ab2b96d8cd2739a6",
        "fffce3e99a75f2ec3ce83a574c8712e8d478ef0d5a91bdb26cc80b83940df0db602520cfa0992ae072518fa98514d5aa78691ff53fe759f618de99b7124e3d70",
        "fffd0eb178fa59b6b9221cdd5ece13494a08ecd480d1c2ef5d003eb92397b7464e1d5dda85e4a19de50b252c53ab8ef198f1133bbc62aebabb99b9dd33480449"
    ],
    "maski": [
        "XX10100055YYYXXXXXXXXXXXXX",
        "XX10100068YYYYXXXXXXXXXXXX",
        "XX10200032YYYXXXXXXXXXXXXX",
        "XX10200032YYYYXXXXXXXXXXXX",
        "XX10200045YYXXXXXXXXXXXXXX"
    ]
}

已编写的部分SQL代码 #

DECLARE @JSON VARCHAR(MAX);
DECLARE @Maski VARCHAR(MAX);
SELECT @JSON = BulkColumn
FROM OPENROWSET(BULK 'D:\20240123.json', SINGLE_CLOB) import
SELECT @Maski = JSON_QUERY(@JSON, '$.maski');

完整解决方案代码 #

1. 创建目标表（若尚未存在） #

CREATE TABLE TMaski (
    MaskValue VARCHAR(100) NOT NULL -- 根据实际mask长度调整字段长度
);

2. 读取JSON并插入数据到表中 #

DECLARE @JSON VARCHAR(MAX);

-- 读取JSON文件内容
SELECT @JSON = BulkColumn
FROM OPENROWSET(BULK 'D:\20240123.json', SINGLE_CLOB) import;

-- 解析maski数组并插入到TMaski表
INSERT INTO TMaski (MaskValue)
SELECT value
FROM OPENJSON(@JSON, '$.maski')
WITH (
    value VARCHAR(100) '$' -- 指定数组元素的类型和路径
);

代码说明 #

• OPENJSON函数可直接解析JSON数组，将每个数组元素拆分为单独行，替代原代码中仅返回JSON片段的JSON_QUERY。
• WITH子句用于指定返回列的类型和对应JSON路径，这里'$'表示直接取数组中的每个元素值。
• 若需重复执行插入，可先添加TRUNCATE TABLE TMaski;清空表数据，避免重复插入。

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