SQLite has built-in support for handling JSON values, with a variety of functions and operators available. There are 15 scalar functions and operators, including json_extract, json_insert, json_remove, and json_type. Two aggregate SQL functions, json_group_array and json_group_object, are also provided. SQLite stores JSON as ordinary text and does not support a binary encoding. The JSON functions were made default in SQLite version 3.38.0 and can be omitted with a compile-time option. The functions can handle JSON objects, arrays, numbers, strings, and null values. They also support JSON5 extensions and have limitations on the depth of nesting. The json() function converts raw text into JSON and the json_array() function creates a JSON array from its arguments. The json_array_length() function returns the length of a JSON array. The json_error_position() function detects syntax errors in JSON strings. The json_extract() function extracts values from JSON, while the -> and ->> operators are used to access subcomponents of JSON. Note that there are subtle differences between the json_extract() function in SQLite and MySQL. The functions provide various features and compatibility with other databases, making JSON handling convenient in SQLite.
https://www.sqlite.org/json1.html