[SQLite] JSON :: 마인드스케일

SQLite는 JSON을 일반 텍스트로 저장합니다. 여러 가지 JSON 함수들을 이용해, JSON 데이터를 다룰 수 있습니다.

json

json(X) 함수는 인수 X가 유효한 JSON 문자열 또는 JSONB blob인지 확인하고 불필요한 공백을 모두 제거한 해당 JSON 문자열의 축소된 버전을 반환합니다.

json(' { "this" : "is", "a": [ "test" ] } ') → '{"this":"is","a":["test"]}'

jsonb

버전 3.45.0(2024-01-15)부터 SQLite에서는 JSON의 내부 "구문 분석 트리" 표현을 "JSONB"라는 형식의 BLOB로 디스크에 저장할 수 있습니다. JSON에 대한 SQLite의 내부 이진 표현을 데이터베이스에 직접 저장함으로써 애플리케이션은 JSON 값을 읽고 업데이트할 때 JSON을 구문 분석하고 렌더링하는 오버헤드를 우회할 수 있습니다. 내부 JSONB 형식도 텍스트 JSON보다 약간 적은 디스크 공간을 사용합니다.

텍스트 JSON을 입력으로 허용하는 모든 SQL 함수 매개변수는 JSONB 형식의 BLOB도 허용합니다. 이 함수는 두 경우 모두 동일하게 작동합니다. 단, 입력이 JSONB일 때 JSON 구문 분석기를 실행할 필요가 없기 때문에 더 빠르게 실행된다는 점이 다릅니다.

"JSONB"라는 이름은 PostgreSQL에서 따왔지만 두 DBMS의 JSONB 형식은 호환되지 않습니다. JSONB는 SQLite의 내부용 포맷이므로, SQLite 외부에서 JSONB를 사용하는 것은 권장되지 않습니다.

jsonb(X) 함수는 인수 X로 제공된 JSON의 이진 JSONB 표현을 반환합니다

json_array

json_array 함수는 인자를 JSON 배열을 반환합니다.

json_array(1,2,'3',4) → '[1,2,"3",4]'

jsonb_array 함수는 인자를 JSONB 배열을 반환합니다.

json_array_length

json_array_length 함수는 JSON 배열의 길이를 반환합니다.

json_array_length('[1,2,3,4]') → 4

json_error_position

json_error_positionf(X) 함수는 입력 X가 올바른 형식의 JSON인 경우 0을 반환합니다. 입력 X에 하나 이상의 구문 오류가 포함된 경우 이 함수는 첫 번째 구문 오류의 문자 위치를 반환합니다.

json_extract

json_extract 함수는 JSON에서 하나 이상의 값을 추출하고 반환합니다.

json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.c.f') → '[null ,7]'

jsonb_extract 함수는 JSONB를 반환합니다.

-> 연산자는 json_extract 함수와 비슷한 기능을 합니다.

'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c.f' → '7'

->> 연산자는 결과값을 SQL의 TEXT, INTEGER, REAL 또는 NULL로 반환합니다.

'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c.f' → 7

json_insert

json_insert 함수는 JSON 객체에 새로운 값을 삽입합니다.

json_insert('[1,2,3,4]','$[#]',99) → '[1,2,3,4,99]'

#은 배열 끝 위치를 나타냅니다.

jsonb_insert 함수는 JSONB를 반환합니다.

json_replace

json_replace와 JSON 객체의 값을 변경합니다.

json_replace('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}'

jsonb_replace 함수는 JSONB를 반환합니다.

json_set

json_set 함수는 JSON 객체의 값을 설정합니다. 만약 기존에 존재하면 변경합니다.

json_set('{"a":2}', '$.c', 4) → '{"a":2,"c":4}'

jsonb_set 함수는 JSONB를 반환합니다.

json_object

json_object() 함수는 인자의 쌍으로 JSON 개체를 만듭니다.

json_object('a',2,'c',4) → '{"a":2,"c":4}'

jsonb_object 함수는 JSONB를 반환합니다.

json_patch

json_patch 함수는 JSON 객체에 대한 패치를 적용합니다. 두 번째 객체를 첫 번째 객체에 덮어 쓴다고 생각하면 됩니다.

json_patch('{"a":1,"b":2}','{"b":3,"d":4}') → '{ "a":1,"b":3,"d":4}'

jsonb_patch 함수는 JSONB를 반환합니다.

json_remove

json_remove 함수는 JSON 객체에서 특정 키를 제거합니다.

json_remove('[0,1,2,3,4]','$[2]') → '[0,1,3,4]'

jsonb_remove 함수는 JSONB를 반환합니다.

json_type

json_type 함수는 JSON 값의 유형을 반환합니다.

json_type('{"a":[2,3.5,true,false,null,"x"]}') → 'object'

json_valid

json_valid(X,Y) 함수는 인수 X가 올바른 형식의 JSON이면 1을 반환하고, X가 형식이 올바르지 않으면 0을 반환합니다.

Y는 이 함수의 설정을 제어하는 옵션으로 생략할수 있습니다.

json_valid('{"x":35}') → 1

json_quote

json_quote 함수는 SQL 값을 JSON 표현으로 변환합니다.

json_quote('verdant') → '"verdant"'

json_each

json_each 함수는 JSON 배열의 각 요소에 대해 하나의 행을 반환합니다.

예를 들어 사용자의 전화번호를 JSON을 이용해 배열 형태로 저장했다고 가정해봅시다.

SELECT DISTINCT user.name
  FROM user, json_each(user.phone)
 WHERE json_each.value LIKE '704-%';

위와 같이 하면, 사용자의 전화 번호 중에 704로 시작하는 번호를 한 행씩 각각(each) 출력합니다.

json_tree

json_tree 함수는 JSON 객체를 순회합니다.

SELECT * FROM json_tree('{ "name" : "hong", "age" : 16 }');

위와 같이하면 결과는 아래와 같습니다.

+------+--------------------------+---------+------+----+--------+---------+------+
| key  |          value           |  type   | atom | id | parent | fullkey | path |
+------+--------------------------+---------+------+----+--------+---------+------+
| null | {"name":"hong","age":16} | object  | null | 0  | null   | $       | $    |
| name | hong                     | text    | hong | 2  | 0      | $.name  | $    |
| age  | 16                       | integer | 16   | 4  | 0      | $.age   | $    |
+------+--------------------------+---------+------+----+--------+---------+------+