JSON转SQL解析问题：含首尾[]的JSON处理及SliderIcons动态解析

针对你遇到的两个问题，我整理了两种场景的实用方案，先看SQL的优化，再给Delphi的字符串处理方法：

一、SQL直接处理外层带[]的JSON #

你的示例JSON外层是一个数组（[]包裹），里面只有一个包含CareNotes数组的对象。完全不需要手动删除[]，只要在OPENJSON里指定正确的路径即可：

优化后的SQL代码：

USE CMUtility;
GO

DECLARE @JSON VARCHAR(MAX);
-- 直接读取带[]的JSON文件，无需预处理
SELECT @JSON = BulkColumn 
FROM OPENROWSET(BULK 'C:\Users\User\Documents\Embarcadero\Studio\Projects\CMU\Win32\Debug\carenotesreport.json', SINGLE_CLOB) AS j;

DROP TABLE IF EXISTS JSONCareNotes;
DROP TABLE IF EXISTS JSONSliderIcons;

-- 1. 导入CareNotes主数据
SELECT 
    DateDone,
    ServiceUser,
    ServiceUserLastName,
    ServiceUserDateofBirth AS SUDOB,
    Note,
    ADLName,
    FlagsText,
    Fragment,
    RemedialText,
    Worker,
    ServiceUserID,
    WorkerID,
    CareNoteID
INTO JSONCareNotes
FROM OPENJSON(@JSON, '$[0].CareNotes') -- 重点：定位到外层数组第一个元素下的CareNotes
WITH (
    DateDone NVARCHAR(10) '$.DateDone',
    ServiceUser NVARCHAR(100) '$.ServiceUser',
    ServiceUserLastName NVARCHAR(50) '$.ServiceUserLastName',
    ServiceUserDateofBirth NVARCHAR(15) '$.ServiceUserDateofBirth',
    Note NVARCHAR(255) '$.Note',
    ADLName NVARCHAR(200) '$.ADLName',
    FlagsText NVARCHAR(255) '$.FlagsText',
    Fragment NVARCHAR(255) '$.Fragment',
    RemedialText NVARCHAR(255) '$.RemedialText',
    Worker NVARCHAR(30) '$.Worker',
    ServiceUserID NVARCHAR(100) '$.ServiceUserID',
    WorkerID NVARCHAR(100) '$.WorkerID',
    CareNoteID NVARCHAR(255) '$.CareNoteID'
) AS CareNotes;

-- 2. 动态解析SliderIcons到关联表（支持任意数量的Icon元素）
SELECT 
    c.CareNoteID,
    s.IconID,
    s.CareNoteText
INTO JSONSliderIcons
FROM JSONCareNotes c
-- 关联到原JSON中的SliderIcons数组
CROSS APPLY OPENJSON(@JSON, '$[0].CareNotes')
WITH (
    CareNoteID NVARCHAR(255) '$.CareNoteID',
    SliderIcons NVARCHAR(MAX) AS JSON -- 将SliderIcons标记为JSON类型以便后续解析
) AS si
CROSS APPLY OPENJSON(si.SliderIcons)
WITH (
    IconID INT '$.IconID',
    CareNoteText NVARCHAR(255) '$.CareNoteText'
) AS s
WHERE c.CareNoteID = si.CareNoteID;

关键说明： #

• $[0].CareNotes：直接定位到外层数组的第一个元素（也就是包含CareNotes的对象）下的CareNotes数组，完美绕过外层[]的问题。
• 用CROSS APPLY OPENJSON(si.SliderIcons)动态展开数组，不管数组有多少个元素，都会生成对应的行记录，比固定字段的方式灵活太多。如果之后需要把多个Icon转成列展示，可以用PIVOT语法实现。

二、Delphi中可靠移除JSON首尾[]的方法 #

如果你还是需要在Delphi里预处理JSON字符串，这里有个精准的方法，只会删除外层的括号，不会误删JSON内容里的括号：

function CleanJSONOuterArray(const AJSON: string): string;
var
  LTrimmed: string;
begin
  LTrimmed := Trim(AJSON);
  // 仅当首尾是[]时才去除
  if (Length(LTrimmed) >= 2) and (LTrimmed[1] = '[') and (LTrimmed[Length(LTrimmed)] = ']') then
    Result := Trim(Copy(LTrimmed, 2, Length(LTrimmed) - 2))
  else
    Result := LTrimmed;
end;

// 使用示例
var
  LRawJSON: string;
begin
  LRawJSON := TFile.ReadAllText('carenotesreport.json');
  LRawJSON := CleanJSONOuterArray(LRawJSON);
  // 此时LRawJSON就是去除外层[]的干净JSON
end;

说明： #

• 先Trim去除首尾的空格、换行符，避免因为文件格式导致判断错误。
• 精准判断首尾字符是否为[和]，只删除外层的括号，完全不影响JSON内部的结构。

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