Блог

Главная - Вопросы по программированию - Использование нескольких перекрестных соединений с JSON, похоже, замедляет мой запрос и никогда не завершается

Использование нескольких перекрестных соединений с JSON, похоже, замедляет мой запрос и никогда не завершается

#sql #json #sql-server

Вопрос:

У меня есть следующий JSON:

 [
  {
    "UserDetails": [
      {
        "UserName": "User1",
        "UserDateOfBirth": "06/11/89",
        "UserID": "12345",
        "NotesDay1": [
          {
            "NoteID": "a287fcc4",
            "AnswerType": 1,
            "RemedialText": null,
            "Details": null,
            "UserLocation": 0,
            "DateDone": "2021-08-06T00:19:14"
          },
          {
            "NoteID": "4a48385a",
            "AnswerType": 1,
            "RemedialText": null,
            "Details": null,
            "UserLocation": 0,
            "DateDone": "2021-08-06T02:19:59"
          },
          {
            "NoteID": "ddb6bc52",
            "AnswerType": 1,
            "RemedialText": null,
            "Details": null,
            "UserLocation": 0,
            "DateDone": "2021-08-06T04:14:50"
          }
        ]
      },
      {
        "UserName": "User2",
        "UserDateOfBirth": "05/08/99",
        "UserID": "23456",
        "NotesDay1": [],
        "NotesDay2": [
          {
            "NoteID": "62cf5478",
            "AnswerType": 1,
            "RemedialText": null,
            "Details": null,
            "UserLocation": 0,
            "DateDone": "2021-08-07T01:00:48"
          },
          {
            "NoteID": "7f864ef4",
            "AnswerType": 1,
            "RemedialText": null,
            "Details": null,
            "UserLocation": 0,
            "DateDone": "2021-08-07T01:00:48"
          },
          {
            "NoteID": "db1a0af0",
            "AnswerType": 1,
            "RemedialText": null,
            "Details": null,
            "UserLocation": 0,
            "DateDone": "2021-08-07T06:28:02"
          }
         ],
        "NotesDay3": [
          {
            "NoteID": "2ae6b923",
            "AnswerType": 1,
            "RemedialText": null,
            "Details": null,
            "UserLocation": 0,
            "DateDone": "2021-08-07T06:28:02"
          }
        ],
        "NotesDay4": [],
        "NotesDay5": [],
        "NotesDay6": [],
        "NotesDay7": []
      }
    ]
  }
]

Мой текущий SQL-запрос для импорта настроен следующим образом:

 SELECT j2.UserID, j3.NoteID1, j4.NoteID2, j5.NoteID3, j6.NoteID4, j7.NoteID5, j8.NoteID6, j9.NoteID7
INTO [UserDayNotes]
FROM OPENJSON(@JSON) 
WITH
    (
        UserDetails nvarchar(max) '$.UserDetails' as JSON
    ) j1
    
CROSS APPLY OPENJSON(j1.UserDetails) WITH
(
        UserID nvarchar(100) '$.UserID',
        NotesDay1 nvarchar(max) '$.NotesDay1' as JSON,
        NotesDay2 nvarchar(max) '$.NotesDay2' as JSON,
        NotesDay3 nvarchar(max) '$.NotesDay3' as JSON,
        NotesDay4 nvarchar(max) '$.NotesDay4' as JSON,
        NotesDay5 nvarchar(max) '$.NotesDay5' as JSON,
        NotesDay6 nvarchar(max) '$.NotesDay6' as JSON,
        NotesDay7 nvarchar(max) '$.NotesDay7' as JSON
) j2
    
CROSS APPLY OPENJSON(j2.NotesDay1) WITH
(
        NoteID1 nvarchar(100) '$.NoteID'
) j3
    
CROSS APPLY OPENJSON(j2.CareNotesDay2) WITH
(
        NoteID2 nvarchar(100) '$.NoteID'
) j4
    
CROSS APPLY OPENJSON(j2.CareNotesDay3) WITH
(
        NoteID3 nvarchar(100) '$.NoteID'
) j5
    
CROSS APPLY OPENJSON(j2.CareNotesDay4) WITH
(
        NoteID4 nvarchar(100) '$.NoteID'
) j6

CROSS APPLY OPENJSON(j2.CareNotesDay5) WITH
(
        NoteID5 nvarchar(100) '$.NoteID'
) j7

CROSS APPLY OPENJSON(j2.CareNotesDay6) WITH
(
        NoteID6 nvarchar(100) '$.NoteID'
) j8 

CROSS APPLY OPENJSON(j2.CareNotesDay7) WITH
(
        NoteID3 nvarchar(100) '$.NoteID'
) j9

Если я выполню запрос как есть, то он будет выполняться вечно и никогда не завершится. Если я закомментирую все, кроме j2.userId, j3.NoteID1 и j4.NoteID2, то запрос выполняется более или менее мгновенно. Я предполагаю, что это как-то связано с количеством элементов перекрестного применения, которые у меня есть, но мой опыт и знания этой стороны SQL невелики, и мне действительно не помешала бы помощь.

После обсуждений ниже я понял, что у меня может быть несколько экземпляров Дня 1, Дня 2 и т.д., И поэтому более реалистичный желаемый результат будет ниже:

Идентификатор пользователя Число дней примечание.
Пользователь1 1 a287fcc4
Пользователь1 1 4a48385a
Пользователь1 1 ddb6bc52
Пользователь2 2 62cf5478
Пользователь2 2 7f864ef4
Пользователь2 2 db1a0af0
Пользователь2 3 2ae6b923

(Боже милостивый, с этим столом пришлось повозиться!)

Данные внутри каждого объекта NotesDayX являются повторением информации, хранящейся в другом месте, поэтому, пока у меня есть идентификатор примечания, я могу обойтись без другой информации, содержащейся в JSON.

Заранее спасибо.

Комментарии:

1. Итак, ваш внешний корневой объект-это массив, всегда ли у него только один индекс или несколько? Всегда ли каждое из свойств NotesDay массива также содержит только один объект?

2.Если подумать, в этом JSON есть что-то очень странное: объект, расположенный в нем, $[0].UserDetails[1] также содержит свойство UserDetails, другими словами, он вложен во второй раз, в то время как первый объект-нет. Является ли этот JSON правильным примером, или где-то отсутствуют или есть дополнительные скобки?

3. @Charlieface Я изменил исходный JSON, чтобы включить более анонимную структуру. В первый раз я немного упростил его для целей этого поста, и хотя он все еще немного отредактирован, структура больше похожа на то, как она выглядит полностью.

4. По сути, UserDetails-это массив с несколькими объектами, содержащими информацию о пользователе. Внутри этих объектов находится дополнительный набор массивов, которые отслеживают данные в течение 7 дней, каждый из которых называется NotesDay 1…NotesDay7. Мне действительно нужна только строка NoteID внутри каждого из них. Каждый NotesDayX может содержать несколько или не содержать объектов, указывающих на записи в этот день, основанные на различных действиях, выполняемых пользователем.

5. Хорошо, поэтому, пожалуйста, ответьте: внешний массив всегда содержит только один объект в нем? И учитывая, что в настоящее время в массиве имеется несколько объектов note NotesDay , как вы хотите это представить?

Ответ №1:

Вы могли бы объединить все Notes массивы вместе.

Кроме того, неясно, всегда ли во внешнем массиве есть только один внутренний объект, если да, то мы можем использовать $[0] и пропустить первый OPENJSON 

 SELECT
    j1.UserID,
    j2.DayNumber,
    j2.NoteID
INTO [UserDayNotes]
FROM OPENJSON(@JSON, '$[0].UserDetails')
  WITH
  (
        UserID nvarchar(100),
        NotesDay1 nvarchar(max) as JSON,
        NotesDay2 nvarchar(max) as JSON,
        NotesDay3 nvarchar(max) as JSON,
        NotesDay4 nvarchar(max) as JSON,
        NotesDay5 nvarchar(max) as JSON,
        NotesDay6 nvarchar(max) as JSON,
        NotesDay7 nvarchar(max) as JSON
) j1
CROSS APPLY (
    SELECT 1 AS DayNumber, NoteID
    FROM OPENJSON(j1.NotesDay1) WITH
    (
        NoteID nvarchar(100)
    ) j2
    UNION ALL
    SELECT 2 AS DayNumber, NoteID
    FROM OPENJSON(j1.NotesDay2) WITH
    (
        NoteID nvarchar(100)
    ) j2
    UNION ALL
    SELECT 3 AS DayNumber, NoteID
    FROM OPENJSON(j1.NotesDay3) WITH
    (
        NoteID nvarchar(100)
    ) j2
    UNION ALL
    SELECT 4 AS DayNumber, NoteID
    FROM OPENJSON(j1.NotesDay4) WITH
    (
        NoteID nvarchar(100)
    ) j2
    UNION ALL
    SELECT 5 AS DayNumber, NoteID
    FROM OPENJSON(j1.NotesDay5) WITH
    (
        NoteID nvarchar(100)
    ) j2
    UNION ALL
    SELECT 6 AS DayNumber, NoteID
    FROM OPENJSON(j1.NotesDay6) WITH
    (
        NoteID nvarchar(100)
    ) j2
    UNION ALL
    SELECT 7 AS DayNumber, NoteID
    FROM OPENJSON(j1.NotesDay7) WITH
    (
        NoteID nvarchar(100)
    ) j2
) j2;

Комментарии:

1. Вот и все!!! Спасибо. Я думаю, что это, вероятно, гораздо более простое и очевидное решение для большинства присутствующих здесь людей, включая вас. Но, как я уже сказал, это не моя обычная работа! Большое вам спасибо @Charlieface

Ответ №2:

..если структура json немного отличается и если в массивах userdetails и notesdayX может быть только один объект …поиграйте:

 declare @j nvarchar(max) =N'
[
{"UserDetails":[
{"UserID":"User1",
"NotesDay1":[{"NoteID":"12345"}],
"NotesDay2":[],
"NotesDay3":[],
"NotesDay4":[{"NoteID":"23456"}],
"NotesDay5":[],
"NotesDay6":[{"NoteID":"34567"}],
"NotesDay7":[{"NoteID":"45678"}]
}
]},
{"UserDetails":[
{"UserID":"User2",
"NotesDay1":[{"NoteID":"54321"}],
"NotesDay2":[{"NoteID":"65432"}],
"NotesDay3":[],
"NotesDay4":[{"NoteID":"76543"}],
"NotesDay5":[],
"NotesDay6":[],
"NotesDay7":[{"NoteID":"87654"}]
}
]}
]';

select *
from openjson(@j)
with
(
userid nvarchar(100) '$.UserDetails[0].UserID',
notesday1 nvarchar(10) '$.UserDetails[0].NotesDay1[0].NoteID',
notesday4 nvarchar(10) '$.UserDetails[0].NotesDay4[0].NoteID',
notesday7 nvarchar(10) '$.UserDetails[0].NotesDay7[0].NoteID'
);

Комментарии:

1. Спасибо за ответ. Однако не будет ли это просто возвращать только первую строку моих данных JSON?

2. @Dan..если массив состоит из пользовательских данных: [{detail1:[]}, {detail2:[]}] .., то openjson() вернет две строки, по одной для каждой детали. в примере @json в вопросе второй пользовательский элемент вложен в первый..и для этого @json даже запрос вопроса не будет работать.

3. Я проверил предложенный вами запрос, и он действительно вернул только одну строку. Исходный запрос выше не работает в сочетании с более чем 2 разными днями, но для первых двух он работал. Вот почему я застрял, лол.

Метки: , ,