[SQL] JSON 데이터 타입의 레코드 중첩 된 배열 쿼리 조합

JSON 데이터 타입의 레코드 중첩 된 배열 쿼리 조합

나는 포스트 그레스 JSON 데이터 유형을 활용하는 레일 응용 프로그램에서 일하고 있어요. 나는 테이블라는 보고서에서 데이터라는 JSON 열 수 있습니다. 이제 나는이 같은 여러 항목이 있다고 가정 해 봅시다 :

Entry 1: {"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barB.png", "pos": "top"}],   "background":"background.png"}
Entry 2: {"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barC.png", "pos": "top"}],   "background":"bacakground.png"}
Entry 3: {"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}
Entry 4: {"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 3, "src":"barB.png", "pos": "top"}],   "background":"backgroundA.png"}

내가 뭘하고 싶은 같은 앨범, SRC, 및 배경이 항목의 다른 조합 돌려입니다 (참고 : (가) 노드 객체 내에서, 배열 요소의 순서는 중요하지 않습니다). 예를 들어, 쿼리가 목표는 상위 3 가장 일반적인 조합을 발견하는 등 다른로서, 한 그룹, 항목 2로 항목을 1,3 일치하고 있습니다. , 그 다음으로 반복 온통 루비를 사용하여이 작업을 수행하는 방법을 알고,하지만 난 많은 항목 샘플을 조회해야합니다. 그것은이 작업을 처리 할 수 ​​있는지 포스트 그레스를 사용하는 것이 더 효율적 보인다. 나는이 가능한 경우 알아야 할 SQL 전문가의 부족입니다.

이것은 내가 찾고 있어요 결과입니다. 뿐만 아니라 모두가, { "barB.png" "앨범": 2, "SRC"} 개체 내에서 항목 1과 3은 {: 1, "SRC" "fooA.png" "앨범"} 포함 모두 일치하는 배경입니다. 나는 그룹 2의 수와 그들과 같은 하나 개의 조합을하고 싶습니다.

항목이이 기준에 따라 어떤 항목과 일치하지 않기 때문에, 또한 난 후 결과가 될 것이다 그래서 1의 수와 다른 조합으로 간주됩니다 1. 항목 4의 수와 다른 조합입니다 :

ids  |  count
--------------
1,3  | 2
2    | 1
4    | 1

또는

combinations                                                                                                                               | count
---------------------------------------------------------------------------------------------------------------------------------------------------
{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},  {"album": 2, "src":"barB.png", "pos": "top"}],  "background":"background.png"}  | 2
{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},  {"album": 2, "src":"barC.png", "pos": "top"}],  "background":"bacakground.png"} | 1
{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},  {"album": 3, "src":"barB.png", "pos": "top"}],  "background":"backgroundA.png"} | 1

어느 달성하기 쉽습니다.

내 실제 데이터에서 나는 개체 노드 내에서 JSON의 배열에 바로 앨범 SRC 이외의 값을 가지고있다. 당신은 내가이 사건을 보여 POS를 포함 한 것을 알 수 있습니다. 난 단지 콤보를 일치하도록 앨범, SRC, 및 배경 값을 사용하는 방법에 대한 관심. 나는 어떤 다른 값을 무시 기대했다.

노트

내가 어윈의 솔루션을 테스트 할 때,이 오류가 계속 나는 이유를 알고 :

내 JSON 값은 실제로는 조금 더 복잡하다. 예를 들면 :

{"objects":[{"album": 1, "src":"fooA.png", "pos": "top", filters: []},  {"album": 2, "src":"barB.png", "pos": "top", filters: []}

물론, 필터는 중첩 된 객체이며 json_populate_recordset 지원하지 않습니다. 그러나, 나는 간단한 대안이없는 경우 나는이 문제를 해결할 수 있다고 생각합니다. 다시 말하지만, 나는이 가능 같은데요?

최신 정보

때문에 내 위의 샘플 데이터에 오타 (내 잘못 함),이 해결책은 다소 불완전하다. 오타가 고정되면, 솔루션은 작동하지 않습니다. 여기에 상황에 대한 답을 찾을 수 있습니다. 그러나 어윈의 솔루션은 여전히 ​​위에서 설명한 것과 유사한 경우에 대한 답변입니다.

해결법

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

   1.(이 같은 형태로 제공해야)이 테이블을 감안할 때 :

   (이 같은 형태로 제공해야)이 테이블을 감안할 때 :

   CREATE TABLE reports (rep_id int primary key, data json);
   INSERT INTO reports (rep_id, data)
   VALUES 
     (1, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barB.png", "pos": "top"}],   "background":"background.png"}')
   , (2, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barC.png", "pos": "top"}],   "background":"bacakground.png"}')
   , (3, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}')
   , (4, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 3, "src":"barB.png", "pos": "top"}],   "background":"backgroundA.png"}')
   ;
   

   레코드 "객체"unnesting에 대한 사용 json_populate_recordset (). 함수는 결과 열 이름 및 데이터 유형을 정의하는 등록 행 유형을 요구한다. 이 데모의 목적 또는 일반적으로 임시 쿼리, "객체"를 모델로 임시 테이블은 동일합니다 :

   CREATE TEMP TABLE obj(album int, src text, pos text);
   

   상위 3 가장 일반적인 조합 ... 같은 앨범, SRC, 및 배경이 항목을 찾으려면 :

   SELECT array_agg(r.rep_id) AS ids, count(*) AS ct
   FROM   reports r
        , json_populate_recordset(null::obj, r.data->'objects') o
   GROUP  BY r.data->>'background'
           , o.album
           , o.scr
   ORDER  BY count(*) DESC
   LIMIT  3;
   

   각 객체 개수에 상관없이 동일한 행의 여부. 당신은 정확하게 처리하는 방법을 정의하지 않았다. 따라서 rep_id 배열 ID를 여러 번 팝업있다. 가능한 중복을 접을 array_agg ()에 DISTINCT 추가합니다. 카운트 CT는이 경우의 배열 ID의 길이가 클 수있다.

   포스트 그레스 9.3은 JSON 함수와 연산자와 암시 적 측면 가입을 위해 필요합니다.

   json_array_elements ()은 단지 행으로 SQL 결과를 변환없이 JSON 배열 unnests. 이에 따라 JSON 사업자와 액세스 개별 필드.

   SELECT array_agg(r.rep_id) AS ids, count(*) AS ct
   FROM   reports r
        , json_array_elements(r.data->'objects') o
   GROUP  BY r.data->>'background'
           , o->>'album'
           , o->>'scr'
   ORDER  BY count(*) DESC
   LIMIT  3;

from https://stackoverflow.com/questions/26877241/query-combinations-with-nested-array-of-records-in-json-datatype by cc-by-sa and MIT license