首页 / 技术问答 / Sql问答列表 / 问答详情

解析SQL Server中的嵌套JSON

我有下面的json，其中可以有多辆车，每辆车可以包含多个行程.我需要插入车辆在TVHC表，并为每辆车插入到TTRP表行程.

[
  {
    "VHC_VTY_CDE": "CAR", 
    "VHC_NAT_CDE": "USA", 
    "VHC_MAN_YEAR": "2020",         
    "VHC_PLT_NBR": "ABC123", 
    "VHC_PLT_NAT_CDE": "USA",
    "VHC_STA_ID": "1",
    "VHC_STA_CDE": "P",
    "VHC_COL_CDE":"",
    "Trips":[
        {
            "TRP_DTE":"2024-01-30",
            "TRP_TIME":"12:00:00.0000000",
            "TRP_BRD_CDE":"061",
            "TRP_AIR_CDE":"QTR",
            "TRP_SHP_CDE":"S12",
            "TRP_FGT_NBR":"CargoXYZ",
            "TRP_SHP_NBR":"S12"
        },
        {
            "TRP_DTE":"2024-01-30",
            "TRP_TIME":"12:00:00.0000000",
            "TRP_BRD_CDE":"061",
            "TRP_AIR_CDE":"QTR",
            "TRP_SHP_CDE":"S12",
            "TRP_FGT_NBR":"CargoXYZ",
            "TRP_SHP_NBR":"S12"
        }
    ]
  }
]

我正在使用以下过程插入数据，但收到以下错误:

Msg 102, Level 15, State 1, Procedure Usama_Test, Line 81 [Batch Start Line 0]
Incorrect syntax near ')'

CREATE OR ALTER PROCEDURE [dbo].[insertVehicles]
    @vehicles_trips_json NVARCHAR(MAX),
    @TRV_ID AS BIGINT
AS
--BEGIN TRANSACTION

    INSERT INTO TVHC 
        (
            VHC_TRV_ID,
            VHC_VTY_CDE,            
            VHC_NAT_CDE,
            VHC_MAN_YEAR,
            VHC_PLT_NBR,
            VHC_PLT_NAT_CDE,
            VHC_STY_ID,
            VHC_STA_CDE,
            VHC_COL_CDE         
        )
        
        SELECT  
                @TRV_ID,
                VHC_VTY_CDE,
                VHC_NAT_CDE,
                VHC_MAN_YEAR,
                VHC_PLT_NBR,
                VHC_PLT_NAT_CDE,
                VHC_STY_ID,
                VHC_STA_CDE,
                VHC_COL_CDE
            FROM
                OPENJSON(@vehicles_trips_json)
                WITH (  
                     [VHC_VTY_CDE]   char(3) N'$.VHC_VTY_CDE',
                     [VHC_NAT_CDE]   char(3) N'$.VHC_NAT_CDE',
                     [VHC_MAN_YEAR]   char(4) N'$.VHC_MAN_YEAR',
                     [VHC_PLT_NBR]   varchar(20) N'$.VHC_PLT_NBR',
                     [VHC_PLT_NAT_CDE]      char(3) N'$.VHC_PLT_NAT_CDE',
                     [VHC_STY_ID]   int N'$.VHC_STY_ID',
                     [VHC_STA_CDE]      int N'$.VHC_STA_CDE',
                     [VHC_COL_CDE]   char(10) N'$.VHC_COL_CDE',
                     [Trips]   nvarchar(max) N'$.Trips'
                    ) AS ROOT_JSON

                OUTER APPLY
                (
                    INSERT INTO TTRP 
                    (
                        TRP_DTE,
                        TRP_TIME,
                        TRP_VHC_ID,
                        TRP_BRD_CDE,
                        TRP_AIR_CDE,
                        TRP_SHP_CDE,
                        TRP_FGT_NBR,
                        TRP_SHP_NBR
                    )
                
                    SELECT  
                            TRP_DTE,
                            TRP_TIME,
                            @@IDENTITY,
                            TRP_BRD_CDE,
                            TRP_AIR_CDE,
                            TRP_SHP_CDE,
                            TRP_FGT_NBR,
                            TRP_SHP_NBR
                        FROM
                            OPENJSON(ROOT_JSON.Trips)
                            WITH (  
                                 [TRP_DTE]   date N'$.TRP_DTE',
                                 [TRP_TIME]   time(7) N'$.TRP_TIME',
                                 [TRP_BRD_CDE]   char(3) N'$.TRP_BRD_CDE',
                                 [TRP_AIR_CDE]   char(3) N'$.TRP_AIR_CDE',
                                 [TRP_SHP_CDE]      char(3) N'$.TRP_SHP_CDE',
                                 [TRP_FGT_NBR]      varchar(20) N'$.TRP_FGT_NBR',
                                 [TRP_SHP_NBR]   varchar(20) N'$.TRP_SHP_NBR'
                                ) 
                )   
--COMMIT TRANSACTION

有什么好主意吗？

这是一个语法错误，我try 了多种变体，但都不起作用.

https://www.codelabs365.com/sql-cookbook/sql-server/tsql-examples-parse-nested-json-with-openjson-function-using-cross-apply-techniques/个

推荐答案

您不能将一个INSERT放入另一个INSERT中(除非在某些情况下使用OUTPUT子句).

相反，您需要使用单独的语句并再次拆分JSON.

CREATE OR ALTER PROCEDURE [dbo].[insertVehicles]
  @vehicles_trips_json NVARCHAR(MAX),
  @TRV_ID AS BIGINT
AS

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRAN;

INSERT INTO TVHC 
(
  VHC_TRV_ID,
  VHC_VTY_CDE,            
  VHC_NAT_CDE,
  VHC_MAN_YEAR,
  VHC_PLT_NBR,
  VHC_PLT_NAT_CDE,
  VHC_STY_ID,
  VHC_STA_CDE,
  VHC_COL_CDE         
)
SELECT  
  @TRV_ID,
  VHC_VTY_CDE,
  VHC_NAT_CDE,
  VHC_MAN_YEAR,
  VHC_PLT_NBR,
  VHC_PLT_NAT_CDE,
  VHC_STY_ID,
  VHC_STA_CDE,
  VHC_COL_CDE
FROM
  OPENJSON(@vehicles_trips_json)
  WITH (  
    VHC_VTY_CDE char(3),
    VHC_NAT_CDE char(3),
    VHC_MAN_YEAR char(4),
    VHC_PLT_NBR varchar(20),
    VHC_PLT_NAT_CDE char(3),
    VHC_STY_ID int,
    VHC_STA_CDE int,
    VHC_COL_CDE char(10)
  ) AS ROOT_JSON;

DECLARE @id int = SCOPE_IDENTITY();

INSERT INTO TTRP 
(
  TRP_DTE,
  TRP_TIME,
  TRP_VHC_ID,
  TRP_BRD_CDE,
  TRP_AIR_CDE,
  TRP_SHP_CDE,
  TRP_FGT_NBR,
  TRP_SHP_NBR
)
SELECT  
  TRP_DTE,
  TRP_TIME,
  @id,
  TRP_BRD_CDE,
  TRP_AIR_CDE,
  TRP_SHP_CDE,
  TRP_FGT_NBR,
  TRP_SHP_NBR
FROM
  OPENJSON(@vehicles_trips_json, N'$[0].Trips')
  WITH (  
    TRP_DTE   date,
    TRP_TIME   time(7),
    TRP_BRD_CDE   char(3),
    TRP_AIR_CDE   char(3),
    TRP_SHP_CDE   char(3),
    TRP_FGT_NBR   varchar(20),
    TRP_SHP_NBR   varchar(20)
  );

COMMIT TRANSACTION;

SCOPE_IDENTITY()不是@@IDENTITY如果有多个外部数组对象，那么它会变得更加复杂，因为您需要在表变量中使用OUTPUT子句来捕获Identity值以及某种标识符.

但你可能需要MERGE的诀窍才能让OUTPUT正常工作.

CREATE OR ALTER PROCEDURE [dbo].[insertVehicles]
  @vehicles_trips_json NVARCHAR(MAX),
  @TRV_ID AS BIGINT
AS

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRAN;

DECLARE @trips TABLE (id int PRIMARY KEY, trips nvarchar(max));

WITH ROOT_JSON AS (
  SELECT *
  FROM
    OPENJSON(@vehicles_trips_json)
    WITH (  
      VHC_VTY_CDE char(3),
      VHC_NAT_CDE char(3),
      VHC_MAN_YEAR char(4),
      VHC_PLT_NBR varchar(20),
      VHC_PLT_NAT_CDE char(3),
      VHC_STY_ID int,
      VHC_STA_CDE int,
      VHC_COL_CDE char(10),
      Trips nvarchar(max) AS JSON
    ) AS ROOT_JSON
)
MERGE TVHC
USING ROOT_JSON
ON 1=0    --never match
WHEN NOT MATCHED
THEN INSERT
(
  VHC_TRV_ID,
  VHC_VTY_CDE,            
  VHC_NAT_CDE,
  VHC_MAN_YEAR,
  VHC_PLT_NBR,
  VHC_PLT_NAT_CDE,
  VHC_STY_ID,
  VHC_STA_CDE,
  VHC_COL_CDE         
)
VALUES (
  @TRV_ID,
  VHC_VTY_CDE,
  VHC_NAT_CDE,
  VHC_MAN_YEAR,
  VHC_PLT_NBR,
  VHC_PLT_NAT_CDE,
  VHC_STY_ID,
  VHC_STA_CDE,
  VHC_COL_CDE
)
OUTPUT inserted.VHC_ID, ROOT_JSON.Trips
INTO @trips (id, trips);

INSERT INTO TTRP 
(
  TRP_DTE,
  TRP_TIME,
  TRP_VHC_ID,
  TRP_BRD_CDE,
  TRP_AIR_CDE,
  TRP_SHP_CDE,
  TRP_FGT_NBR,
  TRP_SHP_NBR
)
SELECT  
  j.TRP_DTE,
  j.TRP_TIME,
  t.id,
  j.TRP_BRD_CDE,
  j.TRP_AIR_CDE,
  j.TRP_SHP_CDE,
  j.TRP_FGT_NBR,
  j.TRP_SHP_NBR
FROM
  @trips t
CROSS APPLY
  OPENJSON(t.Trips)
  WITH (  
    TRP_DTE   date,
    TRP_TIME   time(7),
    TRP_BRD_CDE   char(3),
    TRP_AIR_CDE   char(3),
    TRP_SHP_CDE   char(3),
    TRP_FGT_NBR   varchar(20),
    TRP_SHP_NBR   varchar(20)
  ) j;

COMMIT TRANSACTION;

Sql相关问答推荐

判断时间之间是否有时间

PG SQL中按条件聚合值

GROUP BY与多个嵌套查询T—SQL

为什么在这种情况下我不能使用LAG函数？

SQL将 Select 查询作为新列添加到另一个 Select 查询

将重复的值更新为下一个空闲数字

SQL Select 最小优先级

在SQL中为两个日期之间的每个日期添加行

如何为该查询编写正确分区依据

PostgreSQL：查找继承表中的特定记录属于哪个表

当active=1时，是否可以创建id为的唯一索引？

SQL：使用年/月/日分区查询某个时间段的数据

Proc SQL Select Distinct SAS

SQL JSON_QUERY 使用列中的值构造 json 路径并接收错误

snowflake中的动态文件名生成

自动生成计算频率的列

使用其他表 SUM 的交换价格转换价格并获得 AVG

忽略与给定列匹配的行的 LAG 函数

SQL Group By 然后映射出是否存在值

如何在 Trino/Presto 中过滤掉 map 中的某些键？

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630