title: SQL Server - recursively get into nested JSON array using cross apply or openjson or anything
body: <p>I've been reading as many questions and answers as possible of the same problem but I guess my question would require more creative approach.  </p>
  
  <p>So I have a JSON string here:  </p>
  
  <pre><code>declare @json nvarchar(max) =
  '{
      "propertyObjects": [{
          "propertyID": 1
          , "title": "foo"
          , "class": ""
          , "typeid": 150
          , "value": "bar"
          , "children": [{}]
      }, {
          "propertyID": 2
          , "title": "foo"
          , "class": ""
          , "typeid": 128
          , "value": "bar"
          , "children": [{}]
      }, {
          "propertyID": 3
          , "title": "foo"
          , "class": ""
          , "typeid": 128
          , "value": "bar"
          , "children": [{
              "propertyID": 4
              , "title": "foo"
              , "class": ""
              , "typeid": 128
              , "value": "bar"
              , "children": [{}]
          }, {
              "propertyID": 5
              , "title": "foo"
              , "class": ""
              , "typeid": 128
              , "value": "bar"
              , "children": [{}]
          }, {
              "propertyID": 6
              , "title": "foo"
              , "class": ""
              , "typeid": 128
              , "value": "bar"
              , "children": [{
                  "propertyID": 7
                  , "title": "foo"
                  , "class": ""
                  , "typeid": 128
                  , "value": "bar"
                  , "children": [{
                      "propertyID": 8
                      , "title": "foo"
                      , "class": ""
                      , "typeid": 128
                      , "value": "bar"
                      , "children": [{}]
                  }]
              }]
          }]
      }]
  }'
  </code></pre>
  
  <p>It's crazy at first sight, but think of it this way:<br>
  there's an array called <strong>propertyObjects</strong> which contains multiple objects in parent-child structure.<br>
  In each level, only one object can be parent. As you can see object 3 has children inside.  </p>
  
  <p>What I want here is to list these objects in a table while we specify a parentID for each of them, so object 4 has a parent with ID 3 and object 3 itself got parent 0 because it's basically on top level.  </p>
  
  <p>So far I tried a few approaches like <strong>Common Table Expression</strong> to make a recursive call but I failed:  </p>
  
  <pre><code>;with cte 
  as
  (
      -- anchor member definition
      select p.propertyID
          , 0 as parentID
          , p.title
          , p.typeid
          , p.[value]
          , p.children
      from openjson(@json, '$.propertyObjects')
      with (
          propertyID int
          , title nvarchar(100)
          , typeid int
          , [value] nvarchar(1000)
          , children nvarchar(max) as JSON
      ) as p
  
      UNION ALL
  
      -- recursive member definition
      select 0 as propertyID
          , 0 as parentID
          , '' as title
          , 0 typeid
          , '' as [value]
          , '' as children
      /** child should be bound to parent **/
  )
  select * from cte
  </code></pre>
  
  <p>Here's where I failed, I don't know how to make it recursively find objects through children. Plus, I have no idea how to specify parentID of each children!</p>
  
  <pre><code>propertyID    parentID    title    typeid    value    children
  ----------------------------------------------------------------------------
  1             0           foo      150       bar      [{}]
  2             0           foo      128       bar      [{}]
  3             0           foo      128       bar      [{ "propertyID" : 4 ...
  0             0                    0   
  </code></pre>
  
  <p>I also tried using cross apply:  </p>
  
  <pre><code>select *
  from 
      openjson(@json, '$.propertyObjects')
      with (
          propertyID int
          , title nvarchar(100)
          , typeid int
          , [value] nvarchar(1000)
          , children nvarchar(max) as JSON
      ) as p
  cross apply
      openjson(p.children)
      with (
          propertyID int
          , title nvarchar(100)
          , typeid int
          , [value] nvarchar(1000)
          , children nvarchar(max) as JSON
      ) as r
  </code></pre>
  
  <p>But not a chance, I don't know how deep these children will go in JSON string. besides, results from cross apply will append columns not rows which causes a giant table in result, and in this approach I couldn't even consider specifying parentIDs.</p>
  
  <p>This is totally a failure, any idea on how to get all children in rows?</p>
  
  <p><strong>Desired Table</strong></p>
  
  <pre><code>propertyID    parentID    title    typeid    value
  --------------------------------------------------
  1             0           foo      150       bar
  2             0           foo      128       bar
  3             0           foo      128       bar
  4             3           foo      128       bar
  5             3           foo      128       bar
  6             3           foo      128       bar
  7             6           foo      128       bar
  8             7           foo      128       bar
  </code></pre>