CODEWITHSUNDEEP
sqljsonsql-serverstored-procedures
Stamatis Tiniakos
Stamatis Tiniakos

Reputation: 843

Avoid using multiple INSERT statements when inserting data from JSON to database table

I have written a stored procedure on SQL Server that takes as an input the following JSON post:

{"sentiment_results": {"polarity": -0.6, "subjectivity": 0.7, "emotions": {"anger": 0.08296050131320953, "disgust": 0.00219865539111197, "fear": 0.07708118110895157, "joy": 0.757244884967804, "surprise": 0.027166856452822685, "sadness": 0.05334791541099548}}, "sentiment_time": "2020-08-04T16:43:47.141948"}

...and is using the following script to enter the data on a database table (post_metric_score table -> one row for each datapoint)

         INSERT INTO [STAGING].[post_metric_score]([post_id],[metric_id],[score])
            SELECT @post_id, 1, try_convert(decimal(12, 8), [score])
            FROM OPENJSON(@postJson, '$.sentiment_results')
                WITH ([score] FLOAT '$.polarity')

            INSERT INTO [STAGING].[post_metric_score]([post_id],[metric_id],[score])
            SELECT @post_id, 2, try_convert(decimal(12, 8), [score])
            FROM OPENJSON(@postJson, '$.sentiment_results')
                WITH ([score] FLOAT '$.subjectivity')

            INSERT INTO [STAGING].[post_metric_score]([post_id],[metric_id],[score])
            SELECT @post_id, 3, try_convert(decimal(12, 8), [score])
            FROM OPENJSON(@postJson, '$.sentiment_results.emotions')
                WITH ([score] FLOAT '$.anger')

            INSERT INTO [STAGING].[post_metric_score]([post_id],[metric_id],[score])
            SELECT @post_id, 4, try_convert(decimal(12, 8), [score])
            FROM OPENJSON(@postJson, '$.sentiment_results.emotions')
                WITH ([score] FLOAT '$.disgust')

            INSERT INTO [STAGING].[post_metric_score]([post_id],[metric_id],[score])
            SELECT @post_id, 5, try_convert(decimal(12, 8), [score])
            FROM OPENJSON(@postJson, '$.sentiment_results.emotions')
                WITH ([score] FLOAT '$.fear')

            INSERT INTO [STAGING].[post_metric_score]([post_id],[metric_id],[score])
            SELECT @post_id, 6, try_convert(decimal(12, 8), [score])
            FROM OPENJSON(@postJson, '$.sentiment_results.emotions')
                WITH ([score] FLOAT '$.joy')

The script works fine but it uses a lot of CPU because is running the same insert query 6 times per JSON post.

Is there a way to simplify the script above so it does not have to run the insert statement multiple times?

Upvotes: 0

Views: 270

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can open the JSON once, fetch the columns you want and unpivot using apply. Here is an example with two columns:

INSERT INTO [STAGING].[post_metric_score]([post_id], [metric_id], [score])
    SELECT @post_id, v.metric_id, try_convert(decimal(12, 8), v.score)
    FROM OPENJSON(@postJson, '$.sentiment_results')
         WITH (polarity FLOAT '$.polarity',
               subjectivity FLOAT '$.subjectivity'
              ) as o CROSS APPLY
         (VALUES (1, o.polarity), (2, o.subjectivity)
         ) v(metric_id, score);

With all the columns:

INSERT INTO [STAGING].[post_metric_score]([post_id], [metric_id], [score])
    SELECT @post_id, v.metric_id, try_convert(decimal(12, 8), v.score)
    FROM OPENJSON(@postJson, '$.sentiment_results')
         WITH (polarity FLOAT '$.polarity',
               subjectivity FLOAT '$.subjectivity',
               anger FLOAT '$.emotions.anger',
               disgust FLOAT '$.emotions.disgust',
               fear FLOAT '$.emotions.fear',
               joy FLOAT'$.emotions.joy'
              ) as o CROSS APPLY
         (VALUES (1, o.polarity), (2, o.subjectivity), (3, o.anger), (4, o.disgust), (5, o.fear), (6, o.joy)
         ) v(metric_id, score);

Here is a SQL fiddle.

Upvotes: 1

Zhorov
Zhorov

Reputation: 29943

Another option is using JSON_VALUE() (note, that OPENJSON() is probably faster than using JSON_VALUE() six times):

INSERT INTO [STAGING].[post_metric_score]([post_id], [metric_id], [score])
SELECT @postId, [metric_id], TRY_CONVERT(decimal(12, 8), [score])
FROM (VALUES 
    (1, JSON_VALUE(@postJson, '$.sentiment_results.polarity')),
    (2, JSON_VALUE(@postJson, '$.sentiment_results.subjectivity')),
    (3, JSON_VALUE(@postJson, '$.sentiment_results.emotions.anger')),
    (4, JSON_VALUE(@postJson, '$.sentiment_results.emotions.disgust')),
    (5, JSON_VALUE(@postJson, '$.sentiment_results.emotions.fear')),
    (6, JSON_VALUE(@postJson, '$.sentiment_results.emotions.joy'))
) v ([metric_id], [score])

Upvotes: 1

Related Questions