Extract Fields from JSON and Upsert into Table Using SingleStore Stored Procedure

{question}

How to extract specific fields from a JSON object that is passed as a parameter to a stored procedure and insert them into a SingleStore table?

{question}

{answer}

If a row with the same primary key already exists, the procedure will update the appropriate columns instead.

Example Table Definition

Create a table to store the extracted JSON data:

CREATE TABLE json_data (
ID INT,
Name VARCHAR(50),
Date DATETIME(6),
SHARD KEY `__SHARDKEY` (`ID`),
UNIQUE KEY `__PRIMARY` (`ID`) USING HASH,
KEY `__UNORDERED` () USING CLUSTERED COLUMNSTORE
);

This table employs a hash-based unique key on the column for efficient lookups and utilizes a columnstore index to optimize analytical queries.

Stored Procedure: Extract and Insert JSON Fields

The following stored procedure takes a JSON array as input and inserts or updates records in the json_data table:

DELIMITER //

CREATE OR REPLACE PROCEDURE insert_json_data(q QUERY (List JSON))
AS
BEGIN
INSERT INTO json_data (ID, Name, Date)
SELECT 
table_col::$ID AS ID,
table_col::$Name AS Name,
table_col::$Date AS Date
FROM q
JOIN TABLE(JSON_TO_ARRAY(List::List)) AS table_col

ON DUPLICATE KEY UPDATE
Name = VALUES(Name),
Date = VALUES(Date);
END //

DELIMITER ;

How does it work?

• Input: The procedure accepts a QUERY parameter containing a JSON array in a column named List.

• Parsing: JSON_TO_ARRAY(List::List) converts the JSON array into a table of rows.

• Extraction: Specific fields (ID, Name, Date) are extracted from each JSON object using table_col::$FieldName.

• Insert or Update: If a record with the same ID exists, the Name and Date Fields are updated. Otherwise, a new row is inserted.

Sample Input JSON

Here’s an example of what the JSON input might look like when calling the procedure:

[
{ "ID": 1, "Name": "Alice", "Date": "2025-05-08T10:00:00" },
{ "ID": 2, "Name": "Bob", "Date": "2025-05-08T11:30:00" }
]

This JSON can be passed into the List field when the procedure is initiated.

Conclusion

This approach provides a clean and efficient way to process JSON data within SingleStore stored procedures. It is especially useful for ingesting structured JSON from APIs or streaming sources into normalized relational tables.

{answer}