Get multiple records per row from JSON array

Question

I cannot get my head around how to extract multiple values from a single row, where there source is in a JSON array in every row.

The setup may be a little bit silly, but that's how it is.

Table:

LogID       [int]
LogContent  nvarchar(max)

Contents:

╔═══════╦══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ LogID ║ LogContent (JSON in nvarchar)                                                                                        ║
╠═══════╬══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ 1     ║ [{"DateTime":"2020-04-15T00:00:31","PropertyIWant":"ABC"}, {"DateTime":"2020-04-15T00:00:32","PropertyIWant":"DEF"}] ║
║ 2     ║ [{"DateTime":"2020-04-15T00:00:33","PropertyIWant":"GHI"}, {"DateTime":"2020-04-15T00:00:34","PropertyIWant":"JKL"}] ║
╚═══════╩══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Result I would like:

╔═══╦═════════════════════╦═══════════════╗
║   ║ DateTime            ║ PropertyIWant ║
╠═══╬═════════════════════╬═══════════════╣
║ 1 ║ 2020-04-15T00:00:31 ║ ABC           ║
║ 2 ║ 2020-04-15T00:00:32 ║ DEF           ║
║ 3 ║ 2020-04-15T00:00:33 ║ GHI           ║
║ 4 ║ 2020-04-15T00:00:34 ║ JKL           ║
╚═══╩═════════════════════╩═══════════════╝

I've tried some things with CROSS APPLY and JSON_QUERY, but no luck until now.

An example of it was:

SELECT jt.PropertyIWant FROM Table CROSS APPLY
(
    SELECT * FROM OPENJSON (Table.LogContent) WITH (DateTime datetimeoffset](7), PropertyIWant nvarchar(255) '$.PropertyIWant')
) jt

But this will return 219.851 rows, when there are only 77 rows in the database. In my database it should return around 13.000 records.

Answer 1

Provided your DB version is 2016+, then you can use JSON_QUERY nested in OPENJSON function containing WITH Clause explaining the model for returning columns :

SELECT DateTime, PropertyIWant
  FROM tab
  CROSS APPLY OPENJSON(JSON_QUERY(LogContent, '$'))
           WITH (DateTime      nvarchar(500) '$.DateTime',
                 PropertyIWant nvarchar(500) '$.PropertyIWant');

Demo