SQL JSON 지원

SQL JSON 지원 (SQL JSON support)

SQL 플러그인은 PartiQL 사양을 따라 JSON을 지원해요. PartiQL은 SQL 호환 질의 언어로, 어떤 데이터 형식이든 반정형(semi-structured) 및 중첩 데이터를 질의할 수 있게 해주는 언어예요. SQL 플러그인은 PartiQL 사양의 일부 하위 집합만 지원한다는 점을 기억해 주세요.

출처: 문서

본문

중첩 컬렉션 질의하기

PartiQL은 SQL을 확장해서 중첩 컬렉션을 질의하고 평탄화(flatten)할 수 있게 해줘요. OpenSearch에서 이는 중첩 객체나 필드가 있는 JSON 인덱스를 질의할 때 매우 유용해요.

함께 따라 해 보려면 bulk 연산으로 몇 가지 샘플 데이터를 인덱싱하세요.

POST employees_nested/_bulk?refresh
{"index":{"_id":"1"}}
{"id":3,"name":"Bob Smith","title":null,"projects":[{"name":"SQL Spectrum querying","started_year":1990},{"name":"SQL security","started_year":1999},{"name":"OpenSearch security","started_year":2015}]}
{"index":{"_id":"2"}}
{"id":4,"name":"Susan Smith","title":"Dev Mgr","projects":[]}
{"index":{"_id":"3"}}
{"id":6,"name":"Jane Smith","title":"Software Eng 2","projects":[{"name":"SQL security","started_year":1998},{"name":"Hello security","started_year":2015,"address":[{"city":"Dallas","state":"TX"}]}]}

예시 1: 중첩 컬렉션 평탄화하기

이 예시는 조건(predicate)을 만족하는 필드 값(name)을 가진 중첩 문서(projects)를 찾아요(조건은 security 포함). 각 상위 문서가 둘 이상의 중첩 문서를 가질 수 있기 때문에, 조건에 맞는 중첩 문서가 평탄화돼요. 즉, 최종 결과는 상위 문서와 중첩 문서 사이의 카테시안 곱(Cartesian product)이에요.

SELECT e.name AS employeeName,
       p.name AS projectName
FROM employees_nested AS e,
       e.projects AS p
WHERE p.name LIKE '%security%'

Explain:

{
  "from" : 0,
  "size" : 200,
  "query" : {
    "bool" : {
      "filter" : [
        {
          "bool" : {
            "must" : [
              {
                "nested" : {
                  "query" : {
                    "wildcard" : {
                      "projects.name" : {
                        "wildcard" : "*security*",
                        "boost" : 1.0
                      }
                    }
                  },
                  "path" : "projects",
                  "ignore_unmapped" : false,
                  "score_mode" : "none",
                  "boost" : 1.0,
                  "inner_hits" : {
                    "ignore_unmapped" : false,
                    "from" : 0,
                    "size" : 3,
                    "version" : false,
                    "seq_no_primary_term" : false,
                    "explain" : false,
                    "track_scores" : false,
                    "_source" : {
                      "includes" : [
                        "projects.name"
                      ],
                      "excludes" : [ ]
                    }
                  }
                }
              }
            ],
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        }
      ],
      "adjust_pure_negative" : true,
      "boost" : 1.0
    }
  },
  "_source" : {
    "includes" : [
      "name"
    ],
    "excludes" : [ ]
  }
}

질의는 다음과 같은 결과를 반환해요.

employeeName	projectName
Bob Smith	OpenSearch Security
Bob Smith	SQL security
Jane Smith	Hello security
Jane Smith	SQL security

예시 2: 실존적(existential) 서브쿼리에서의 평탄화

조건을 만족하는지 확인하기 위해 서브쿼리에서 중첩 컬렉션을 평탄화하려면 다음과 같이 해요.

SELECT e.name AS employeeName
FROM employees_nested AS e
WHERE EXISTS (
    SELECT *
    FROM e.projects AS p
    WHERE p.name LIKE '%security%'
)