title: How to parse nested Json in SQL Server
body: <p>I retrieved JSON from an API (part of json file was showed at the bottom). I was hoping to parse the json and store in SQL table. With the following SQL query, there was only 1 row returned. How can I return all rows with table headers NAME JobNum Water Sewer ? I tried while loop using variable to replace <code>[0]</code> after <code>$.items</code>, but seemed not to work. I wasn't sure if the structure of json file works for cross apply.</p>
  <pre><code>DECLARE @MondayComApi VARCHAR(MAX)
  
  SELECT @MondayComApi = BULKCOLUMN
  FROM OPENROWSET(BULK'D:/temp/a.json', SINGLE_BLOB) JSON
  
  IF (ISJSON(@MondayComApi) = 1)
  BEGIN
      PRINT 'JSON File is valid';
  
      SELECT NAME, JobNum, Water, Sewer 
      FROM OPENJSON(@MondayComApi, '$.data.boards')
      WITH (
          NAME VARCHAR(100) '$.items[0].name',
          JobNum VARCHAR(100) '$.items[0].column_values[0].text',
          Water VARCHAR(100) '$.items[0].column_values[1].text',
          Sewer VARCHAR(100) '$.items[0].column_values[2].text'
      )
  END
  ELSE
  BEGIN
      PRINT 'JSON File is invalid';
  END
  </code></pre>
  <p>The following was part of the JSON - I reduced content of &quot;items&quot; to shorten length:</p>
  <pre><code>{
    &quot;data&quot;: {
      &quot;boards&quot;: [
          {
              &quot;items&quot;: [
                  {
                      &quot;name&quot;: &quot;Holmes Project&quot;,
                      &quot;column_values&quot;: [
                          {
                              &quot;title&quot;: &quot;Job&quot;,
                              &quot;text&quot;: &quot;D1210&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Water&quot;,
                              &quot;text&quot;: &quot;YES&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Sewer&quot;,
                              &quot;text&quot;: &quot;YES&quot;
                          }
                      ]
                  },
                  {
                      &quot;name&quot;: &quot;Lake Short Project)&quot;,
                      &quot;column_values&quot;: [
                          {
                              &quot;title&quot;: &quot;Job&quot;,
                              &quot;text&quot;: &quot;D1014&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Water&quot;,
                              &quot;text&quot;: &quot;YES&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Sewer&quot;,
                              &quot;text&quot;: &quot;YES&quot;
                          }
                      ]
                  },
                  {
                      &quot;name&quot;: &quot;Chase Project&quot;,
                      &quot;column_values&quot;: [
                          {
                              &quot;title&quot;: &quot;Job&quot;,
                              &quot;text&quot;: &quot;D2101&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Water&quot;,
                              &quot;text&quot;: &quot;NO&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Sewer&quot;,
                              &quot;text&quot;: &quot;YES&quot;
                          }
                      ]
                  },
                  {
                      &quot;name&quot;: &quot;Juanita Project&quot;,
                      &quot;column_values&quot;: [
                          {
                              &quot;title&quot;: &quot;Job&quot;,
                              &quot;text&quot;: &quot;D1102&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Water&quot;,
                              &quot;text&quot;: &quot;YES&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Sewer&quot;,
                              &quot;text&quot;: &quot;YES&quot;
                          }
                      ]
                  },
                  {
                      &quot;name&quot;: &quot;Lowry Project&quot;,
                      &quot;column_values&quot;: [
                          {
                              &quot;title&quot;: &quot;Job&quot;,
                              &quot;text&quot;: &quot;D1014&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Water&quot;,
                              &quot;text&quot;: &quot;YES&quot;
                          },
                          {
                              &quot;title&quot;: &quot;Sewer&quot;,
                              &quot;text&quot;: &quot;YES&quot;
                          }
                      ]
                  }
              ]
          }
      ]
  },
  &quot;account_id&quot;: 5687438790
  }
  </code></pre>