SQL Server中如何将JSON整数数组插入表整数类型列？

Got it, let's break this down into two clear parts for you:

Assuming you're working with a SQL dialect that supports JSON_TABLE (like Oracle, PostgreSQL, or other SQL/JSON compliant systems), here's how to insert a JSON integer array into individual table rows:

First, create a sample table to hold your integer values:

CREATE TABLE IntegerValues (
    ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    NumericValue INT NOT NULL
);

Then use JSON_TABLE to parse the array and insert each element as a separate row:

INSERT INTO IntegerValues (NumericValue)
SELECT value
FROM JSON_TABLE(
    '[10, 20, 30, 40, 50]', -- Replace with your JSON integer array
    '$[*]' COLUMNS (value INT PATH '$')
);

• '$[*]' targets every element in the top-level array
• The COLUMNS clause maps each array element directly to an integer column named value

SQL Server relies on the OPENJSON function to parse JSON data. Let's cover two common real-world scenarios:

Scenario 1: JSON variable is a pure integer array #

First, set up a sample table (if you don't already have one):

CREATE TABLE dbo.IntStore (
    RecordID INT IDENTITY(1,1) PRIMARY KEY,
    IntValue INT NOT NULL
);

Declare your JSON array variable and use OPENJSON to insert values:

DECLARE @JsonArray NVARCHAR(MAX) = '[15, 25, 35, 45, 55]';

INSERT INTO dbo.IntStore (IntValue)
SELECT CAST(Value AS INT)
FROM OPENJSON(@JsonArray)
-- Optional: Filter out invalid entries to avoid conversion errors
WHERE TRY_CAST(Value AS INT) IS NOT NULL;

• OPENJSON returns array elements as strings, so we cast them to INT
• TRY_CAST ensures we only insert valid integers, skipping any malformed values that can't be converted

Scenario 2: JSON variable is an object with a nested integer array #

If your JSON has a nested array (e.g., {"values": [5, 10, 15, 20]}), use this streamlined approach:

DECLARE @JsonObject NVARCHAR(MAX) = '{"values": [5, 10, 15, 20, 25]}';

INSERT INTO dbo.IntStore (IntValue)
SELECT value
FROM OPENJSON(@JsonObject, '$.values') -- Directly target the nested array path
WITH (value INT '$'); -- Explicitly define the element data type

• The second OPENJSON parameter ('$.values') points directly to your nested array
• The WITH clause removes the need for extra casting by specifying the integer type upfront

Quick Production Tip #

Always validate your JSON first to avoid parsing errors:

IF ISJSON(@JsonArray) = 1
BEGIN
    INSERT INTO dbo.IntStore (IntValue)
    SELECT CAST(Value AS INT)
    FROM OPENJSON(@JsonArray)
    WHERE TRY_CAST(Value AS INT) IS NOT NULL;
END
ELSE
BEGIN
    PRINT 'Invalid JSON format - cannot insert values';
END

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