如何“分解”SQL数据库中已经存在的JSON数据？

Question

我已经成功地将JSON文件导入到SQL 2016数据库中，现在我正在尝试解析该数据，以便可以为保存JSON数据的列中的每个字段填充表。我不是DBA，我花了几天时间才弄清楚如何成功地导入这些数据。我需要知道如何使用SQL来实现这一点。我不确定我需要在这里提供什么其他信息，所以如果需要任何其他信息，请让我知道，我会提供这些信息。

表名为dbo.IncapsulaSourceData。该表有5列: Site_ID、JSON_Source、Processed、Date_inserted、Date_processed。

下面是存储在JSON_Source列中的JSON数据的示例：

{
"site_id":123456,
"statusEnum":"fully_configured",
"status":"fully-configured",
"domain":"site.name.com",
"account_id":111111,
"acceleration_level":"standard",
"site_creation_date":1410815844000,
"ips":[
"99.99.99.99"
],
"dns":[
{
    "dns_record_name":"site.name.com",
    "set_type_to":"CNAME",
    "set_data_to":[
        "frgt.x.wafdns.net"
    ]
}
],
"original_dns":[
{
    "dns_record_name":"name.com",
    "set_type_to":"A",
    "set_data_to":[
        ""
    ]
},
{
    "dns_record_name":"site.name.com",
    "set_type_to":"A",
    "set_data_to":[
        "99.99.99.99"
    ]
},
{
    "dns_record_name":"site.name.com",
    "set_type_to":"CNAME",
    "set_data_to":[
        ""
    ]
}
],
"warnings":[

],
"active":"active",
"additionalErrors":[

],
"display_name":"site.name.com",
"security":{
"waf":{
    "rules":[
        {
            "action":"api.threats.action.block_ip",
            "action_text":"Block IP",
            "id":"api.threats.sql_injection",
            "name":"SQL Injection"
        },
        {
            "action":"api.threats.action.block_request",
            "action_text":"Block Request",
            "id":"api.threats.cross_site_scripting",
            "name":"Cross Site Scripting"
        },
        {
            "action":"api.threats.action.block_ip",
            "action_text":"Block IP",
            "id":"api.threats.illegal_resource_access",
            "name":"Illegal Resource Access"
        },
        {
            "block_bad_bots":true,
            "challenge_suspected_bots":true,
            "exceptions":[
                {
                    "values":[
                        {
                            "ips":[
                                "99.99.99.99"
                            ],
                            "id":"api.rule_exception_type.client_ip",
                            "name":"IP"
                        }
                    ],
                    "id":123456789
                },
                {
                    "values":[
                        {
                            "ips":[
                                "99.99.99.99"
                            ],
                            "id":"api.rule_exception_type.client_ip",
                            "name":"IP"
                        }
                    ],
                    "id":987654321
                }
            ],
            "id":"api.threats.bot_access_control",
            "name":"Bot Access Control"
        },
        {
            "activation_mode":"api.threats.ddos.activation_mode.auto",
            "activation_mode_text":"Auto",
            "ddos_traffic_threshold":1000,
            "id":"api.threats.ddos",
            "name":"DDoS"
        },
        {
            "action":"api.threats.action.quarantine_url",
            "action_text":"Auto-Quarantine",
            "id":"api.threats.backdoor",
            "name":"Backdoor Protect"
        },
        {
            "action":"api.threats.action.block_ip",
            "action_text":"Block IP",
            "id":"api.threats.remote_file_inclusion",
            "name":"Remote File Inclusion"
        },
        {
            "action":"api.threats.action.disabled",
            "action_text":"Ignore",
            "id":"api.threats.customRule",
            "name":"wafRules"
        }
    ]
},
"acls":{
    "rules":[
        {
            "ips":[
                "99.99.99.99"
            ],
            "id":"api.acl.whitelisted_ips",
            "name":"Visitors from whitelisted IPs"
        },
        {
            "geo":{
                "countries":[
                    "BR",                        
                    "NL",
                    "PL",
                    "RO",
                    "RU",
                    "TR",
                    "TW",
                    "UA"
                ]
            },
            "id":"api.acl.blacklisted_countries",
            "name":"Visitors from blacklisted Countries"
        }
    ]
}
},
"sealLocation":{
"id":"api.seal_location.none",
"name":"No seal "
},
"ssl":{
"origin_server":{
    "detected":true,
    "detectionStatus":"ok"
},
"generated_certificate":{
    "ca":"GS",
    "validation_method":"email",
    "validation_data":"administrator@site.name.com",
    "san":[
        "*.site.name.com"
    ],
    "validation_status":"done"
}
},
"siteDualFactorSettings":{
"specificUsers":[

],
"enabled":false,
"customAreas":[

],
"allowAllUsers":true,
"shouldSuggestApplicatons":true,
"allowedMedia":[
    "ga",
    "sms"
],
"shouldSendLoginNotifications":true,
"version":0
},
"login_protect":{
"enabled":false,
"specific_users_list":[

],
"send_lp_notifications":true,
"allow_all_users":true,
"authentication_methods":[
    "ga",
    "sms"
],
"urls":[

],
"url_patterns":[

]
},
"performance_configuration":{
"advanced_caching_rules":{
    "never_cache_resources":[

    ],
    "always_cache_resources":[

    ]
},
"acceleration_level":"standard",
"async_validation":true,
"minify_javascript":true,
"minify_css":true,
"minify_static_html":true,
"compress_jepg":true,
"progressive_image_rendering":false,
"aggressive_compression":false,
"compress_png":true,
"on_the_fly_compression":true,
"tcp_pre_pooling":true,
"comply_no_cache":false,
"comply_vary":false,
"use_shortest_caching":false,
"perfer_last_modified":false,
"accelerate_https":false,
"disable_client_side_caching":false,
"cache300x":false,
"cache_headers":[

]
},
"extended_ddos":1000,
"res":0,
"res_message":"OK",
"debug_info":{
"id-info":"1234"
}
}