JSON 값에 지정된 속성이 포함되어 있는지 확인하기 위한 SQL 쿼리

JSON 값에 지정된 속성이 포함되어 있는지 확인하기 위한 SQL 쿼리

각 행의 JSON 값을 저장하는 컬럼이 있는 테이블이 있습니다.현재 이것들은 표준화 되어 있지 않습니다.마지막으로 각각의 JSON 값을 속성의 수와 제목으로 표준화하고 싶습니다.각 JSON 값에 지정된 속성이 포함되어 있는지 여부를 확인하기 위해 사용할 수 있는 쿼리가 있습니까?

예를 들어 JSON 값의 일부를 다음에 나타냅니다.

{"name":"Item 1","cost":"4.99"}
{"name":"Item 2"}
{"name":"Item 3","cost":""}
{"name":"Item 4"}

어떻게 해야 하나요?

• "비용" 속성이 있는 행을 특정합니다(항목 1 및 항목 3).
• "비용" 속성이 없는 행을 특정합니다(항목 2 및 4).
• "비용" 속성의 값이 설정되어 있는 행을 특정합니다(항목 1).

감사해요!(최소한 최근) 처음 부탁하는 일이기 때문에, 잘 부탁드립니다!

MySQL 5.7을 사용하고 있는 것 같습니다.이것에 의해,JSONdata type 입니다.를 사용하여cost소유물.그럴 것이다.NULL그런 재산이 없다는 거죠

1. WHERE JSON_EXTRACT(colname, '$.cost') IS NOT NULL
2. WHERE JSON_EXTRACT(colname, '$.cost') IS NULL
3. WHERE JSON_EXTRACT(colname, '$.cost') != ''

또한NULLJSON의 값이null; 이 대소문자를 구별할 필요가 있는 경우는, 「JSON_EXTRACT 에서 늘 값을 검출할 수 없습니다」를 참조해 주세요.

json 키가 존재하는지 여부만 확인해야 하는 경우 JSON_CONTENS_PATH를 사용할 수 있습니다.JSON_EXTRACT보다 빠를 것으로 예상됩니다.

mySQL 8.0 문서

예:

DB에 권한 열이 있습니다.구조는 다음과 같습니다.

{
    "users": {
        "user1" : "rw",
        "user2" : "r"
    },
    "groups": {
        "root": "rw",
        "anotherGroup" : "r"
    }
}

(실제 권한과 관련하여) 루트 그룹이 있는 모든 항목을 가져오려면 다음을 사용합니다.

SELECT * FROM `filesystem_folders` where JSON_CONTAINS_PATH(permissions, 'one', '$.groups.root');

12.90s(1만 읽기)가 걸린 위의 선택 항목과 매우 간단한 시간 비교를 했습니다.JSON_EXTRACT를 사용한 다음 선택 항목과 비교

 SELECT * FROM `filesystem_folders` where JSON_EXTRACT(permissions, '$.groups.root') IS NOT NULL;

15.13초 걸렸어요아주 작은 차이입니다.

JSON 함수(5.7 이전) 없이 MySQL을 사용하는 경우 또는 JSON 목록 또는 예기치 않게 중첩된 목록에 태그가 있는지 확인하는 경우.

다음으로 를 사용하여 수행하는 방법의 예를 제시하겠습니다.LIKE:

SELECT case when colname like '%cost%' then 1 else 0 end as has_cost,
       case when colname like '%cost":""%' then 1 else 0 end as has_cost_with_no_value
FROM tablename

실제로 이 작업을 수행할 수 있습니다.JSON_EXTRACT결과는 다음과 같습니다.

WHERE JSON_EXTRACT(colname, '$."cost"') = JSON_EXTRACT(colname, '$."cost"')

JSON 열에 대응하는 키가 없는 경우, 이것은 다소 "false"/null이 됩니다.

이렇게 하면 검색하는 키가 있는 모든 엔트리를 찾을 수 있습니다.키가 없는 모든 엔트리를 원하는 경우 NOT 연산자를 사용할 수 없습니다.다음과 같이 해야 합니다.

SELECT * FROM tablename WHERE id NOT IN (SELECT id FROM tablename WHERE WHERE JSON_EXTRACT(colname, '$."cost"') = JSON_EXTRACT(colname, '$."cost"'))

부두교처럼 보이지만 효과가 있는 것 같아요

MySQL JSON 。그들을 끈처럼 대한다. 때문에 이렇게 '어울릴 수 없다'를 해야 합니다.LIKE.

언급URL : https://stackoverflow.com/questions/39731954/sql-query-to-determine-if-a-json-value-contains-a-specified-attribute