title: How to import JSON into SQL 2016
body: <p>I'm trying something new and could use some guidance.  I have a JSON file full of data that I would like to have imported into SQL 2016.  I can get the entire file into SQL but breaking it apart into a readable format is where I am stuck.  I'm new to SQL, so when reading articles on this subject I am struggling to comprehend.  The following query is what I've used to perform the initial import:</p>
  
  <pre><code>SELECT *
  FROM OPENROWSET (BULK 'c:\tmp\test.json', SINGLE_CLOB) as j
  CROSS APPLY OPENJSON(BulkColumn)
  </code></pre>
  
  <p>Each row is populated but the values contain sub sections that I need to have expanded.  Here is the test data that I am using:</p>
  
  <pre><code>{
  "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",
                          "CN",
                          "DE",
                          "ES",
                          "GB",
                          "HK",
                          "IR",
                          "IT",
                          "KP",
                          "KR",
                          "KZ",
                          "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"
  }
  }
  </code></pre>
  
  <p>I know enough about SQL to know that I am going to have to have multiple tables for these subsections.  How do I select these subsections to expand them out into their own tables?  If I am not clear on the explanation or the question, please comment and I will do my best to be more precise.</p>