JSON to SQL Table with nested properties

Question

I am trying to develop database table structure from following JSON Structure. "requiredfields" are straight forward and I table setup for those. However, I am stuck on how to created schema to store "Configuration" and its nested properties in SQL Database. Any help will be appreciated. Thanks.

{
    "requiredFields": [
        "hello",
        "world"
    ],
    "configuration": {
        "hello": {
            "fallbacks": [
                {
                    "type": "constant",
                    "value": "30"
                }
            ]
        },
        "world": {
            "fallbacks": [
                {
                    "type": "fromInputFile",
                    "value": "patientFirstName"
                },
                {
                    "type": "fromInputFile",
                    "value": "subscriberFirstName"
                },
                {
                    "type": "constant",
                    "value": "alpha"
                }
            ]
        }
    }
}

Charlieface · Accepted Answer · 2022-02-01 09:33:37Z

1

It looks like you could store that in a single table

CREATE TABLE ConfigurationFallbacks (
    field nvarchar(100),
    type nvarchar(100),
    value nvarchar(100)
);

You may also want to add another table to store just the field values, and the table above would be foreign-keyed to that.

You can insert like this:

INSERT ConfigurationFallbacks (field, type, value)
SELECT
  keys.[key],
  j.type,
  j.value
FROM OPENJSON(@json, '$.configuration') keys
CROSS APPLY OPENJSON(keys.value, '$.fallbacks')
  WITH (
    type nvarchar(100),
    value nvarchar(100)    
  ) j;

The first OPENJSON call does not have a schema, so it returns a set of key value pairs.

answered Feb 1, 2022 at 9:33

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

JSON to SQL Table with nested properties

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related