PostgreSQL에서 JSON 배열 내의 특정 요소를 어떻게 쿼리할 수 있습니까?

PostgreSQL에서 JSON 배열 요소 쿼리

PostgreSQL 9.3 이상에서는 JSON 데이터 유형을 지원하므로 JSON 데이터를 데이터베이스에 저장하고 조작할 수 있습니다. 이 기능을 사용하면 복잡한 데이터 구조(예: 개체 배열)를 단일 열에 저장할 수 있습니다. 그러나 이러한 복잡한 구조 내에서 특정 요소나 값을 쿼리하는 것은 어려울 수 있습니다.

"reports" 테이블의 "data"라는 열에 다음 JSON 데이터가 저장되어 있다고 가정합니다.

<code class="language-json">{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}</code>

일반적인 작업은 "objects" 배열의 "src" 필드에서 특정 값을 포함하는 모든 보고서를 검색하는 것입니다. "배경"과 같은 단순 JSON 필드를 쿼리하는 것은 간단하지만 배열의 요소를 추출하려면 다른 접근 방식이 필요합니다.

다음 쿼리는 지정된 "배경" 값과 일치하는 모든 보고서를 성공적으로 검색합니다.

<code class="language-sql">SELECT data AS data FROM reports WHERE data->>'background' = 'background.png'</code>

그러나 유사한 구문(예:)을 사용하여 "객체" 배열의 값을 쿼리해 보세요.

<code class="language-sql">SELECT data AS data FROM reports WHERE data->>'objects' = 'foo.png'</code>

은 "객체"가 단순 필드가 아닌 배열이기 때문에 예상한 결과를 생성하지 않습니다.

JSON 배열의 요소를 성공적으로 쿼리하려면 수평 조인에서 json_array_elements() 함수를 사용할 수 있습니다. 이 함수는 배열에서 단일 요소를 추출하므로 각 요소를 개별적으로 테스트할 수 있습니다.

다음 쿼리는 측면 조인을 사용하여 "objects" 배열을 확장하고 특정 "src" 값이 있는지 테스트합니다.

<code class="language-sql">SELECT data AS data
FROM reports r, json_array_elements(r.data#>'{objects}') obj
WHERE obj->>'src' = 'foo.png';</code>

또는 단일 중첩 수준의 경우 쿼리의 단순화된 버전을 사용할 수 있습니다.

<code class="language-sql">SELECT *
FROM reports r, json_array_elements(r.data->>'objects') obj
WHERE obj->>'src' = 'foo.png';</code>

이러한 쿼리는 #>, -> 및 ->> 연산자를 사용하여 JSON 구조를 탐색합니다. 이러한 연산자에 대한 자세한 내용은 PostgreSQL 설명서를 참조하세요.

또한 배열과 관련된 복잡한 JSON 쿼리의 성능을 최적화하려면 관련 JSON 표현식에 GIN 인덱스를 추가하는 것이 좋습니다. 이는 특히 대규모 데이터 세트의 경우 검색 속도를 크게 향상시킬 수 있습니다.

위 내용은 PostgreSQL에서 JSON 배열 내의 특정 요소를 어떻게 쿼리할 수 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!