How do I query a table in SQL Server that has a JSON column, where that JSON column can result in 0 or Many rows?

I have a table my_table. Here is a simplified view:

id, the_json
1, {json json json}
2, {json}
3, {json json}

Here is a sample of what the JSON can look like:

{
    "valid":false,
    "messages": {
        {
            "message": "blah",
            "error": true,
            "detail": "something1"
        }
        {
            "message": "blah blah",
            "error": true,
            "detail": "something2"
        }
        {
            "message": "blah blah blah",
            "error": false,
            "detail": "something3"
        }
    }
}

I am trying to write a query to get the following result (row gaps for visibility):

id, message, error, detail

1, blah, true, something1
1, blah blah, true, something2
1, blah blah blah, false, something3

2, blah, true, something1

3, blah, true, something1
3, blah blah, true, something2

Aka, each message node in the json should result in a row, and should join back to the parent table to show the id.

You need to use the OPENJSON function to get separate rows, and you need to do it with a schema to get these columns. To get it per row of your table, you need to APPLY it.

Note the use of a second parameter to jumpt straight to $.messages

SELECT
  t.id,
  j.*
FROM YourTable t
CROSS APPLY OPENJSON(t.the_json, '$.messages')
  WITH (
    message nvarchar(100),
    error bit,
    detail nvarchar(100)
  ) j;