Union Results of Multiple OPENJSON calls

Question

I have a table that stores 1 json object per row, I want to call OPENJSON on the json object stored in each row and union together all of the results. I don't know the number of rows I will have ahead of time.

Here is some example data to reference

    DROP TABLE #tmp_json_tbl

DECLARE @json1 NVARCHAR(2048) = N'{
    "members": [
        {
            "name": "bob",
            "status": "subscribed"
        },
        {
            "name": "larry",
            "status": "unsubscribed"
        }
    ]
}';

SELECT @json1 as json_obj,1 as jid into #tmp_json_tbl 

INSERT into #tmp_json_tbl
VALUES ( N'{
    "members": [
        {
            "name": "bob",
            "status": "subscribed"
        },
        {
            "name": "larry",
            "status": "unsubscribed"
        }
    ]
}',2 );

SELECT * from #tmp_json_tbl


--how can i recursively union together results for all values of jid?
-- I could use a cursor but I would rather figure out a way to do it using a recursive cte
SELECT * FROM OpenJson((SELECT json_obj from #tmp_json_tbl where jid=1), '$.members')
WITH (   
             name   VARCHAR(80)   '$.name',  
             mstatus  varchar(100)       '$.status'  
 
)```

Answer 1

This is what I wanted

SELECT name, m_status
FROM   #tmp_json
CROSS APPLY OPENJSON(j, '$.members') 
WITH (   
  name VARCHAR(80)       '$.name',  
  m_status  varchar(100) '$.status'  
)

Found my answer here: How to use OPENJSON on multiple rows