Moving data from relational to JSON columns and vice versa

  • Article

Moving data from relational to JSON columns and vice versa

Unlike other pure relational or pure NoSQL databases, Sql Server do not forces you to store data in relational or JSON format. You can choose any format you want, put some data in scalar columns and other in JSON columns. As an example if you have Person table, you can put primary keys, foreign keys, the most commonly used columns such as FirstName and Last name in scalar columns, and other values that are rarely used in JSON key value pairs. This might be useful if you have some columns that depends on type of Person (e.g. Student can have one set of columns, Teacher another, etc.) Instead of breaking Student and Teacher into different tables or adding all possible columns in Person table, you can put all variable columns in JSON text.

Also, if you change your mind and decide that some value stored as JSON field should be moved to regular column, and vice versa, you can easily move data around, e.g. move data from column into JSON text or move some JSON property into regular column.

As an example, you might find that middle name field stored in JSON text should be stored as standard relational column. You can easily create new column and move values from JSON text on some path to the column:

 ALTER TABLE Person
ADD MiddleName NVARCHAR(200)

UPDATE Person
SET MiddleName = JSON_MODIFY(AdditionalInfo, '$.info.middleName'),
    AdditionalInfo = JSON_MODIFY(AdditionalInfo, '$.info.middleName', NULL)

In this example, we are reading values from $.info.middleName path and store them into new MiddleName column. We are also updating AdditionalInfo JSON text and delete middleName key. Note that by default JSON_MODIFY suppresses NULL values. Whenever you try to set NULL value, JSON_MODIFY will delete key instead of setting null value. This way, JSON_MODIFY automaticaly cleans unnecessary null values.

Also, you might decide to move values from the existing column to some JSON field. In this case you can modify AdditionalInfo column and add these values on some property path:

 UPDATE Person
SET AdditionalInfo = JSON_MODIFY(AdditionalInfo, '$.info.middleName', MiddleName)
GO
ALTER TABLE Person
DROP MiddleName

Once you move data, you can drop the column you don't need.  New JSON_MODIFY function in SQL Server enables you to easily move values between scalar and JSON columns and redesign your schema.