1

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
KoalaBear
  • 2,755
  • 2
  • 25
  • 29
  • These things you've tried, do show us. – Dale K Apr 15 '20 at 04:52
  • 1
    Your statement is fine (althought `CROSS APPLY OPENJSON` is enough). With the data in the question this statement returns 4 rows and this is the correct result, because you have 2 items per row in each JSON array. Can you post test data that returns wrong results? – Zhorov Apr 15 '20 at 05:55
  • Thanks Zhorov, I forgot the `WHERE` statement.. This was causing a lot of other JSON's to be returning NULLs, and also a lot of not expected results. – KoalaBear Apr 15 '20 at 06:08

1 Answers1

2

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

KoalaBear
  • 2,755
  • 2
  • 25
  • 29
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Thanks, I've tested this, and also included it after Dale K requested it. Unfortunately it returns the same amount of records, 219.851. – KoalaBear Apr 15 '20 at 05:53
  • This is strange, made a copy of the table and only copied 3 testrecords to it, then it gives the results as expected, I'll first check why and how. – KoalaBear Apr 15 '20 at 06:02
  • Yes. I'm just stupid.. There were more records in the Log table of other things it logged which I didn't exclude with a `WHERE`.. Thanks! – KoalaBear Apr 15 '20 at 06:04