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

Question

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?

Gordon Linoff · Accepted Answer · 2020-08-05 11:27:42Z

1

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.

edited Aug 5, 2020 at 11:27

answered Aug 5, 2020 at 10:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stamatis Tiniakos Over a year ago

Thank you @GordonLinoff, what about the emotions in the JSON? They exist a bit deeper in the json than polarity and subjectivity e.g. sentiment_results.emotions. Do I have to open the JSON a second time to access them?

Gordon Linoff Over a year ago

@StamatisTiniakos . . . No. You just have to list the paths appropriately.

Zhorov · Accepted Answer · 2020-08-05 11:05:15Z

1

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])

edited Aug 5, 2020 at 11:05

answered Aug 5, 2020 at 10:58

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related