Azure SQL как получить первый элемент в массиве, который удовлетворяет условию

#sql #azure #azure-cosmosdb

Вопрос:

У меня есть коллекция с телом:

 {
    "id": "666",
    "name": "Test",
    "lastEvents": [
        {
            "show": false,
            "eventType": "TYPE_1",
            "creationDateTime": "2020-08-16T11:59:00"
        },
        {
            "show": false,
            "eventType": "TYPE_33",
            "creationDateTime": "2020-10-14T06:11:00",
            "control": "test repaired"
        },
        {
            "show": false,
            "eventType": "TYPE_33",
            "creationDateTime": "2020-10-14T06:08:00",
            "control": "test recovered"
        }
    ],
    "_rid": "kgsjAN-JFWEDQEQWESDAD==",
    "_self": "JFWE=/docs/kgsjAN-JFWEDQEQWESDAD==/dbs/kgsjAA==/colls/kgsjAN",
    "_etag": ""666-0000-750011b7-0000-5eec704f0000"",
    "_attachments": "attachments/",
    "_ts": 1592873510
}

И я хотел бы отфильтровать его по первому lastEvents.control полю. Теперь я использую:

 SELECT * 
FROM root r
WHERE EXISTS(
        SELECT VALUE n                              
        FROM n IN r.lastEventFields                             
        WHERE CONTAINS(n.control, "recovered", true)
)

но этот запрос выполняется через весь массив lastEvent. В моем случае я должен проверять только первый элемент с control помощью поля.
Как я могу добиться желаемого поведения?

Ответ №1:

Если я не ошибаюсь, вы хотите получить документ, который является первым элементом, имеющим control поле в lastEvents массиве, и он должен содержать «восстановленную» строку.

Если это так, SQL не может выполнить ваше требование.

Обходной путь:

1. создайте UDF и lastEvents массив циклов. UDF может стоить дорого, пожалуйста, обратите на это внимание. Если вам нужно, я могу предоставить JS-код.

2. добавьте файл в свой документ, чтобы проверить, соответствует ли он вашим потребностям на стороне клиента, прежде чем сохранять / обновлять документ. Если вы часто обновляете свой документ, это плохая альтернатива.

3. получите все ваши документы и отфильтруйте их на стороне клиента.

1. Нет, потому что я не знаю точного индекса. Он не обязательно будет равен 0.

2. @ArtemDanilin Я обновил свой ответ. Я не уверен, что понимаю ваш смысл. Вы можете посмотреть.

3. Вы правы, @Steve Zhao, здесь нет недоразумений. Я знаю об UDF, и я уже пробовал это. В моем случае это будет стоить слишком дорого! Как вы думаете, есть ли способ решить мою проблему без UDF? Я хотел бы решить это с помощью «собственного SQL»

4. Я пытался использовать такие операторы, как TOP, OFFSET, но это не работает в подзапросах.

5. Я тоже пробовал. Подзапрос не поддерживает TOP, OFFSET. Я понятия не имею, как справиться с этим с помощью SQL.

Ответ №2:

Ваш запрос не возвращает мне никаких данных. Вот способ сделать это по-другому

 SELECT c.id, c.name, s.show, s.eventType, s.control, s.creationDateTime
FROM c
join s IN c.lastEvents
WHERE IS_DEFINED(s.control) AND CONTAINS(s.control, "recovered", true)

1. Да, я допустил ошибку в своем SQL. Но я хотел бы добиться другого результата. В настоящее время мой и ваш SQL находят весь массив lastEvent. Но в моем случае мне нужен только первый элемент массива, где поле ‘control’ является define

2. Если я вас правильно понимаю, вы хотите, чтобы ваш запрос остановил итерацию, как только он найдет то, что вы ищете. Я прав?

3. Я хочу получить документ, который является первым элементом, имеющим поле управления в массиве lastEvents, и он должен содержать «восстановленную» строку. Вы можете обратиться к разговору с @Steve Zhao