OpenJSON

JSON is one of the data formats that you may have to work with in SQL Server. Data elements contained in this format are delimited by square brackets and curly braces. Unlike structured data that is in tables (rows and columns), certain applications prefer to send data to the server in unstructured format, such as XML or JSON. We use OPENJSON function (reminiscent of OPENXML) to parse JSON data and turn it into rows and columns. When extracting data using OPENJSON, we specify the full path to the element. We often have to call OPENJSON recursively using CROSS APPLY because each data element may in turn contain data in JSON format.

Have a look at the following example:

DECLARE @plants NVARCHAR(MAX) =
'[{
   "Plants":
   [{"Fruit":
       [{
        "Apples":"Golden Delicious",
        "Figs":"Brown Turkey",
        "Peaches":"Sweet GA",
        "PrimaryHealthBenefit":"Lower Blood Pressure",
        "Callories":
           [{
             "Large": "100-150",
             "Medium": "50-100",
             "Small": "20-50"
           }]
        }]
     }]
   }]
}]'
SELECT
   apples,
   figs,
   peaches,
   [primary benefit],
   [calories in large],
   [calories in medium] ,
   [calories in small]
FROM OPENJSON (@plants)
        WITH (
         plants NVARCHAR(MAX) '$.Plants' AS JSON
        ) AS plants
        CROSS APPLY OPENJSON (plants, '$')
          WITH (
           fruit NVARCHAR(MAX) '$.Fruit' AS JSON
              ) AS fruit
             CROSS APPLY OPENJSON(fruit, '$')
               WITH (
                 Apples VARCHAR(200) '$.Apples',
                 Figs VARCHAR(200) '$.Figs',
                 Peaches VARCHAR(200) '$.Peaches',
                 [Primary Benefit] VARCHAR(200)         '$.PrimaryHealthBenefit',
                 Calories NVARCHAR(MAX) '$.Callories' AS JSON
                     ) AS Calories
                  CROSS APPLY OPENJSON (Calories, '$')
                    WITH (
                    [calories in large] VARCHAR(200) '$.Large',
                    [calories in medium] VARCHAR(200) '$.Medium',
                    [calories in small] VARCHAR(200) '$.Small')

In this example we parse three JSON constructs: plants, fruit and calories. The word “callories” is misspelled on purpose. The main point is that each JSON construct is called recursively with CROSS APPLY, whereas the elements are identified by passing the path, such as ‘$.Large’ for the calorie count in a large fruit. The query results in a single record that shows each element of the JSON string in separate columns.

Note that JSON constructs are always declared as NVARCHAR(MAX). Much like XML, JSON is case-sensitive, so if you don’t see the result you expect go back and make sure you used the correct case for each element.

Tweaking log shipping restore

Log shipping has been part of SQL Server for many years. Its setup and configuration are straightforward and generally things work well. However, you may notice that you don’t have a lot of flexibility for tweaking log shipping. If you attempt editing the backup or restore jobs created by the wizard, you’ll quickly realize that log shipping is bit of a blackbox. The backup job simply contains the server name and primary id. The restore job contains the server name and the secondary id.

In some cases you may want to adjust the RESTORE LOG statement issued by log shipping executable to speed up the restore and ensure your primary and secondary databases stay in sync. This can be accomplished by updating msdb..log_shipping_secondary_databases table. Relevant columns are block_size, buffer_count and max_transfer_size. Changes are effective during next execution of the log shipping restore job.

Yes, this is a system table, but you’re allowed to update it. Clearly, you should know what you’re doing and test your changes in development environment prior to applying the same changes in production.

You may also be wandering if there is a way to tweak the BACKUP LOG command too. Sadly, the answer is no.