[SQL] JSON 형 내부 배열 요소를 쿼리

JSON 형 내부 배열 요소를 쿼리

나는 PostgreSQL을 9.3에서 JSON 형식을 테스트하기 위해 노력하고있어. 나는 테이블라는 보고서에서 데이터라는 JSON 열 수 있습니다. json으로 다음과 같은 :

{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}

나는 '객체의 배열의'SRC '값과 일치하는 모든 보고서의 테이블을 조회하고 싶습니다. 예를 들어, 일치하는 'SRC'= 'foo.png'모든 보고서에 대한 DB를 조회 할 수 있습니까? 나는 성공적으로 "배경"을 일치시킬 수 있습니다 쿼리를 썼다 :

SELECT data AS data FROM reports where data->>'background' = 'background.png'

"객체"값의 배열을 가지고 있기 때문에, 나는 작품이 쓰기 뭔가 수없는 것. 그것은 일치하는 'SRC'= 'foo.png'모든 보고서에 대한 DB를 조회 할 수 있습니까? 나는이 소스를 통해 검토 한하지만 여전히 그것을 얻을 수 없습니다 :

나는 또한이 좋아하지만 아무 소용이 일을 시도했다 :

SELECT json_array_elements(data->'objects') AS data from reports
WHERE  data->>'src' = 'foo.png';

내가 잘못하고있는 중이 야 모르겠어요 그래서 나는는 SQL 전문가가 아니에요.

해결법

1. ==============================

   1.요소에 대한 절에서 테스트에 참여 대해 측에서 json_array_elements 함수 ()와 JSON 배열 Unnest :

   요소에 대한 절에서 테스트에 참여 대해 측에서 json_array_elements 함수 ()와 JSON 배열 Unnest :

   WITH reports(data) AS (
      VALUES ('{"objects":[{"src":"foo.png"}, {"src":"bar.png"}]
              , "background":"background.png"}'::json)
      ) 
   SELECT *
   FROM   reports r, json_array_elements(r.data#>'{objects}') obj
   WHERE  obj->>'src' = 'foo.png';

   열팽창 계수 (WITH 쿼리) 테이블 보고서 만 대체합니다. 또는 중첩 단지 하나의 레벨에 해당 :

   SELECT *
   FROM   reports r, json_array_elements(r.data->'objects') obj
   WHERE  obj->>'src' = 'foo.png';

   - >> -> 및 #>이 연산자는 매뉴얼에 설명되어 있습니다.

   두 쿼리는 암시 적 측면 가입 사용합니다.

   SQL 바이올린.

   밀접하게 관련 대답 :

   동등한 jsonb_array_elements ()를 사용합니다.

   더 나은 아직, 새로운 연산자를 "포함"를 사용 @> (식 데이터에 일치하는 GIN 인덱스와 함께 최고의 -> '객체') :

   CREATE INDEX reports_data_gin_idx ON reports
   USING gin ((data->'objects') jsonb_path_ops);
   
   SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';
   

   키 객체가 JSON 배열을 보유하고 있기 때문에, 우리는 너무, 검색어의 구조와 일치 대괄호로 배열 요소를 포장해야합니다. 일반 기록을 검색 할 때 배열 브래킷을 놓습니다.

   자세한 설명과 추가 옵션 :

2. ==============================

   2.형 JSON과 같은 열이있는 테이블을 만듭니다

   형 JSON과 같은 열이있는 테이블을 만듭니다

   # CREATE TABLE friends ( id serial primary key, data jsonb);
   

   이제 JSON 데이터 삽입하자

   # INSERT INTO friends(data) VALUES ('{"name": "Arya", "work": ["Improvements", "Office"], "available": true}');
   
   # INSERT INTO friends(data) VALUES ('{"name": "Tim Cook", "work": ["Cook", "ceo", "Play"], "uses": ["baseball", "laptop"], "available": false}');
   

   이제 데이터를 가져 오기 위해 일부 쿼리를 만들어 보자

   # select data->'name' from friends;
   
   # select data->'name' as name, data->'work' as work from friends;
   

   결과가 반전 쉼표 ( ")와 대괄호와 함께 제공 당신은 눈치 챘을 수도 있습니다 ([])

       name    |            work            
   ------------+----------------------------
    "Arya"     | ["Improvements", "Office"]
    "Tim Cook" | ["Cook", "ceo", "Play"]
   (2 rows)
   

   이제 값만 단지 사용을 검색하는 - >>

   # select data->>'name' as name, data->'work'->>0 as work from friends;
   
   #select data->>'name' as name, data->'work'->>0 as work from friends where data->>'name'='Arya';
   

3. ==============================

   3.데이터를 선택 -> '물체'-> 0 -> 'SRC'SRC 표에서 어디에 데이터 -> '물체'-> 0 -> 'SRC'= 'foo.png'

   데이터를 선택 -> '물체'-> 0 -> 'SRC'SRC 표에서 어디에 데이터 -> '물체'-> 0 -> 'SRC'= 'foo.png'

from https://stackoverflow.com/questions/22736742/query-for-array-elements-inside-json-type by cc-by-sa and MIT license