JSON Postgres 데이터 유형 열에서 특정 문자열을 검색하려면 어떻게 해야 합니까?

JSON Postgres 데이터 유형 열에서 특정 문자열을 검색하려면 어떻게 해야 합니까?

라는 컬럼이 있습니다.params라는 테이블에서reportsJSON이 포함되어 있습니다.

JSON 어레이 중 어느 행에 'authVar'라는 텍스트가 포함되어 있는지 확인해야 합니다.텍스트가 표시될 수 있는 경로나 수준을 알 수 없습니다.

JSON을 통해 연산자와 같은 표준으로 검색하고 싶습니다.

예를 들어 다음과 같습니다.

SELECT * FROM reports
WHERE params LIKE '%authVar%'

나는 Postgres 문서를 검색하고 검색해 보았다.JSON의 데이터 타입을 잘 이해하지 못하기 때문에 쉬운 것을 놓치고 있습니다.

JSON은 이렇게 생겼어요.

[  
   {  
      "tileId":18811,
      "Params":{  
         "data":[  
            {  
               "name":"Week Ending",
               "color":"#27B5E1",
               "report":"report1",
               "locations":{  
                  "c1":0,
                  "c2":0,
                  "r1":"authVar",
                  "r2":66
               }
            }
         ]
      }
   }
]

Postgres 11 이전 버전에서는 알려지지 않은 json 구조를 재귀적으로 통과하는 것이 가능하지만 다소 복잡하고 비용이 많이 듭니다.저는 잘 작동해야 할 폭력 방법을 제안합니다.

select *
from reports
where params::text like '%authVar%';
-- or 
-- where params::text like '%"authVar"%';
-- if you are looking for the exact value

쿼리는 매우 빠르지만 검색된 문자열이 키의 일부인 경우 예기치 않은 추가 행을 반환할 수 있습니다.

Postgres 12+에서는 JSONB에서의 재귀 검색은 의 신기능에 매우 적합합니다.jsonpath.

authVar를 포함하는 문자열 값을 찾습니다.

select *
from reports
where jsonb_path_exists(params, '$.** ? (@.type() == "string" && @ like_regex "authVar")')

jsonpath:

$.**                     find any value at any level (recursive processing)
?                        where
@.type() == "string"     value is string
&&                       and
@ like_regex "authVar"   value contains 'authVar'

또는 정확한 값을 찾으십시오.

select *
from reports
where jsonb_path_exists(params, '$.** ? (@ == "authVar")')

다음 문서를 참조하십시오.

• SQL/JSON 경로 언어
• jsonpath 유형

언급URL : https://stackoverflow.com/questions/45849494/how-do-i-search-for-a-specific-string-in-a-json-postgres-data-type-column