Ejemplo de sqlite json

los json1 extensión es una extensión cargable que implementa quince funciones SQL definidas por la aplicación y dos funciones con valores de tabla que son útiles para administrar JSON contenido almacenado en una base de datos SQLite. Hay trece funciones escalares:

1. jsonjson)
2. json_array (valor1,valor2, …)
3. json_array_length (json)
   json_array_length (json,sendero)
4. json_extract (json,sendero, …)
5. json_insert (json,sendero,valor, …)
6. json_object (etiqueta1,valor1, …)
7. json_patch (json1, json2)
8. json_remove (json,sendero, …)
9. json_replace (json,sendero,valor, …)
10. json_set (json,sendero,valor, …)
11. json_type (json)
    json_type (json,sendero)
12. json_valid (json)
13. json_quote (valor)

Hay dos funciones SQL agregadas:

14. json_group_array (valor)
15. json_group_object (nombre,valor)

Las dos funciones con valores de tabla son:

16. json_each (json)
    json_each (json,sendero)
17. json_tree (json)
    json_tree (json,sendero)

La documentación de extensiones cargables describe cómo compilar extensiones cargables como bibliotecas compartidas. Las técnicas descritas allí funcionan bien para el módulo json1.

El código fuente json1 se incluye con la fusión de SQLite, aunque está deshabilitado de forma predeterminada. Agregue la opción de tiempo de compilación -DSQLITE_ENABLE_JSON1 para habilitar la extensión json1 que está integrada en la fusión. Los archivos MAKE estándar incluyen -DSQLITE_ENABLE_JSON1 cuando se construye el shell de la línea de comandos y algunas de las utilidades de prueba, por lo que esta extensión normalmente está disponible en el shell de la línea de comandos.

La extensión json1 (actualmente) almacena JSON como texto normal.

Las restricciones de compatibilidad con versiones anteriores significan que SQLite solo puede almacenar valores que son NULL, enteros, números de punto flotante, texto y BLOB. No es posible agregar un sexto tipo “JSON”.

La extensión json1 no admite (actualmente) una codificación binaria de JSON. Los experimentos no han podido encontrar una codificación binaria que sea significativamente más pequeña o más rápida que una codificación de texto sin formato. (La implementación actual analiza el texto JSON a más de 1 GB / s.) Todas las funciones json1 actualmente arrojan un error si alguno de sus argumentos son BLOB porque los BLOB están reservados para una mejora futura en la que los BLOB almacenarán la codificación binaria para JSON.

El “1” al final del nombre de la extensión json1 es deliberado. Los diseñadores anticipan que habrá futuras extensiones JSON incompatibles basadas en las lecciones aprendidas de json1. Una vez que se gana suficiente experiencia, se puede incorporar algún tipo de extensión JSON al núcleo de SQLite. Por ahora, la compatibilidad con JSON sigue siendo una extensión.

3.1. Argumentos JSON

Para las funciones que aceptan JSON como su primer argumento, ese argumento puede ser un objeto JSON, una matriz, un número, una cadena o un nulo. Los valores numéricos de SQLite y los valores NULL se interpretan como números JSON y nulos, respectivamente. Los valores de texto de SQLite pueden entenderse como objetos, matrices o cadenas de JSON. Si un valor de texto SQLite que no es un objeto JSON, matriz o cadena bien formados se pasa a la función json1, esa función generalmente arrojará un error. (Las excepciones a esta regla son json_valid () y json_quote ().)

Para determinar la validez, se ignoran los espacios en blanco iniciales y finales en las entradas JSON. Los espacios en blanco interiores también se ignoran, de acuerdo con la especificación JSON. Estas rutinas aceptan exactamente el rfc-7159 sintaxis JSON – Nada mas y nada menos.

3.2. Argumentos PATH

Para las funciones que aceptan argumentos PATH, esa PATH debe estar bien formada o, de lo contrario, la función arrojará un error. Una RUTA bien formada es un valor de texto que comienza exactamente con un carácter ‘$’ seguido de cero o más instancias de “.etiqueta de objeto” o “[arrayindex]”.

los índice de matriz suele ser un número entero no negativo norte. En ese caso, el elemento de matriz seleccionado es el norte-th elemento de la matriz, comenzando con cero a la izquierda. los índice de matriz también puede tener la forma “# –norte“en cuyo caso el elemento seleccionado es el norte-th desde la derecha. El último elemento de la matriz es “# -1“. Piense en los caracteres” # “como el” número de elementos en la matriz “. Entonces la expresión” # -1 “evalúa como el número entero que corresponde a la última entrada en la matriz.

3.3. VALUE argumentos

Para funciones que aceptan “valor“argumentos (también mostrados como”valor1” y “valor2“), se suele entender que esos argumentos son cadenas literales entre comillas y se convierten en valores de cadena JSON en el resultado. Incluso si la entrada valor Las cadenas parecen JSON bien formadas, pero aún se interpretan como cadenas literales en el resultado.

Sin embargo, si un valor El argumento proviene directamente del resultado de otra función json1, luego se entiende que el argumento es JSON real y se inserta el JSON completo en lugar de una cadena entre comillas.

Por ejemplo, en la siguiente llamada a json_object (), el valor El argumento parece una matriz JSON bien formada. Sin embargo, debido a que es solo texto SQL ordinario, se interpreta como una cadena literal y se agrega al resultado como una cadena entre comillas:

• json_object (‘ex’, ‘[52,3.14159]’)→ ‘”ex”: “[52,3.14159]” ‘

Pero si el valor El argumento en la llamada externa json_object () es el resultado de otra función json1 como json () o json_array (), entonces se entiende que el valor es JSON real y se inserta como tal:

• json_object (‘ex’, json (‘[52,3.14159]’))→ ‘”ex”:[52,3.14159] ‘
• objeto_json (‘ex’, matriz_json (52,3.14159))→ ‘”ex”:[52,3.14159] ‘

Para ser claro: “json“Los argumentos siempre se interpretan como JSON independientemente de dónde provenga el valor de ese argumento. Pero”valor“Los argumentos solo se interpretan como JSON si esos argumentos provienen directamente de otra función json1.

3.4. Compatibilidad

La extensión json1 usa las interfaces sqlite3_value_subtype () y sqlite3_result_subtype () que se introdujeron con la versión 3.9.0 de SQLite (2015-10-14) La extensión json1 no funcionará en versiones anteriores de SQLite.

La implementación actual de esta biblioteca JSON usa un analizador de descenso recursivo. Para evitar el uso de espacio de pila en exceso, cualquier entrada JSON que tenga más de 2000 niveles de anidamiento se considera inválida. Se permiten límites en la profundidad de anidación para implementaciones compatibles de JSON por RFC-7159 sección 9.

Las siguientes secciones proporcionan detalles adicionales sobre el funcionamiento de las diversas funciones que forman parte de la extensión json1.

4.1. La función json ()

La función json (X) verifica que su argumento X sea una cadena JSON válida y devuelve una versión reducida de esa cadena JSON (con todos los espacios en blanco innecesarios eliminados). Si X no es una cadena JSON bien formada, esta rutina arroja un error.

Si el argumento X a json (X) contiene objetos JSON con etiquetas duplicadas, no está definido si se conservan o no los duplicados. La implementación actual conserva los duplicados. Sin embargo, las mejoras futuras de esta rutina pueden optar por eliminar silenciosamente los duplicados.

Ejemplo:

• json (‘”esto”: “es”, “a”: [ “test” ] ‘)→ ‘”esto”: “es”, “a”:[“test”] ‘

4.2. La función json_array ()

La función de SQL json_array () acepta cero o más argumentos y devuelve una matriz JSON bien formada que se compone de esos argumentos. Si algún argumento de json_array () es un BLOB, se produce un error.

Un argumento con el tipo de SQL TEXT normalmente se convierte en una cadena JSON entre comillas. Sin embargo, si el argumento es el resultado de otra función json1, entonces se almacena como JSON. Esto permite anidar las llamadas a json_array () y json_object (). La función json () también se puede utilizar para forzar que las cadenas se reconozcan como JSON.

Ejemplos:

• json_array (1,2, ‘3’, 4)→ ‘[1,2,”3″,4]’
• json_array (‘[1,2]’)→ ‘[“[1,2]”]’
• json_array (json_array (1,2))→ ‘[[1,2]]’
• json_array (1, nulo, ‘3’, ‘[4,5]’,’ “seis”: 7.7 ‘)→ ‘[1,null,”3″,”[4,5]”,” “seis “: 7.7 “]’
• matriz_json (1, nulo, ‘3’, json (‘[4,5]’), json (‘ “seis”: 7.7 ‘))→ ‘[1,null,”3″,[4,5], “seis”: 7.7]’

4.3. La función json_array_length ()

La función json_array_length (X) devuelve el número de elementos en la matriz JSON X, o 0 si X es algún tipo de valor JSON que no sea una matriz. Json_array_length (X, P) ubica la matriz en la ruta P dentro de X y devuelve la longitud de esa matriz, o 0 si la ruta P ubica un elemento o X que no sea una matriz JSON, y NULL si la ruta P no ubica ningún elemento de X. Se producen errores si X no es JSON bien formado o si P no es una ruta bien formada.

Ejemplos:

• json_array_length (‘[1,2,3,4]’)→ 4
• json_array_length (‘[1,2,3,4]PS→ 4
• json_array_length (‘[1,2,3,4]PS[2]’)→ 0
• json_array_length (‘”uno”:[1,2,3] ‘)→ 0
• json_array_length (‘”uno”:[1,2,3] ‘,’ $ .one ‘)→ 3
• json_array_length (‘”uno”:[1,2,3] ‘,’ $ .two ‘)→ NULO

4.4. La función json_extract ()

El json_extract (X, P1, P2, …) extrae y devuelve uno o más valores del JSON bien formado en X. Si solo se proporciona una única ruta P1, entonces el tipo de datos SQL del resultado es NULL para un JSON nulo, INTEGER o REAL para un valor numérico JSON, un cero INTEGER para un valor falso JSON, uno INTEGER para un valor verdadero JSON, el texto entrecomillado para un valor de cadena JSON y una representación de texto para valores de matriz y objetos JSON. Si hay varios argumentos de ruta (P1, P2, etc.), esta rutina devuelve texto SQLite, que es una matriz JSON bien formada que contiene los distintos valores.

Ejemplos:

• json_extract (‘{“a”: 2, “c”:[4,5,”f”:7]PS→ ‘”a”: 2, “c”:[4,5,”f”:7] ‘
• json_extract (‘”a”: 2, “c”:[4,5,”f”:7] ‘,’ $ .c ‘)→ ‘[4,5,”f”:7]’
• json_extract (‘”a”: 2, “c”:[4,5,”f”:7] ‘,’ $ .c[2]’)→ ‘”f”: 7’
• json_extract (‘”a”: 2, “c”:[4,5,”f”:7] ‘,’ $ .c[2].F’)→ 7
• json_extract (‘”a”: 2, “c”:[4,5], “f”: 7 ‘,’ $. c ‘,’ $. a ‘)→ ‘[[4,5], 2]’
• json_extract (‘”a”: 2, “c”:[4,5], “f”: 7 ‘,’ $. c[#-1]’)→ 5
• json_extract (‘”a”: 2, “c”:[4,5,”f”:7] ‘,’ $ .x ‘)→ NULO
• json_extract (‘”a”: 2, “c”:[4,5,”f”:7] ‘,’ $ .x ‘,’ $ .a ‘)→ ‘[null,2]’

4.5. Las funciones json_insert (), json_replace y json_set ()

Las funciones json_insert (), json_replace y json_set () toman un solo valor JSON como primer argumento seguido de cero o más pares de argumentos de ruta y valor, y devuelven una nueva cadena JSON formada al actualizar el JSON de entrada por la ruta / pares de valores. Las funciones difieren solo en cómo tratan la creación de nuevos valores y la sobrescritura de valores preexistentes.

Las funciones json_insert (), json_replace () y json_set () siempre toman un número impar de argumentos. El primer argumento es siempre el JSON original que se va a editar. Los argumentos posteriores ocurren en pares, siendo el primer elemento de cada par una ruta y el segundo elemento el valor para insertar, reemplazar o establecer en esa ruta.

Las ediciones ocurren secuencialmente de izquierda a derecha. Los cambios causados ​​por ediciones anteriores pueden afectar la búsqueda de ruta para ediciones posteriores.

Si el valor de un par ruta / valor es un valor de SQLite TEXT, entonces normalmente se inserta como una cadena JSON entre comillas, incluso si la cadena parece un JSON válido. Sin embargo, si el valor es el resultado de otra función json1 (como json () o json_array () o json_object ()), entonces se interpreta como JSON y se inserta como JSON conservando toda su subestructura.

Estas rutinas arrojan un error si el primer argumento JSON no está bien formado o si algún argumento PATH no está bien formado o si algún argumento es un BLOB.

Para agregar un elemento al final de una matriz, use json_insert () con un índice de matriz de “#”. Ejemplos:

• json_insert (‘[1,2,3,4]PS[#]’, 99)→ ‘[1,2,3,4,99]’
• json_insert (‘[1,[2,3], 4]’,’ $[1][#]’, 99)→ ‘[1,[2,3,99], 4]’

Otros ejemplos:

• json_insert (‘”a”: 2, “c”: 4’, ‘$ .a’, 99)→ ‘”a”: 2, “c”: 4’
• json_insert (‘”a”: 2, “c”: 4’, ‘$ .e’, 99)→ ‘”a”: 2, “c”: 4, “e”: 99’
• json_replace (‘”a”: 2, “c”: 4’, ‘$ .a’, 99)→ ‘”a”: 99, “c”: 4’
• json_replace (‘”a”: 2, “c”: 4’, ‘$ .e’, 99)→ ‘”a”: 2, “c”: 4’
• json_set (‘”a”: 2, “c”: 4’, ‘$ .a’, 99)→ ‘”a”: 99, “c”: 4’
• json_set (‘”a”: 2, “c”: 4’, ‘$ .e’, 99)→ ‘”a”: 2, “c”: 4, “e”: 99’
• json_set (‘”a”: 2, “c”: 4’, ‘$ .c’, ‘[97,96]’)→ ‘”a”: 2, “c”: “[97,96]” ‘
• json_set (‘”a”: 2, “c”: 4’, ‘$ .c’, json (‘[97,96]’))→ ‘”a”: 2, “c”:[97,96] ‘
• json_set (‘”a”: 2, “c”: 4’, ‘$ .c’, json_array (97,96))→ ‘”a”: 2, “c”:[97,96] ‘

4.6. La función json_object ()

La función SQL json_object () acepta cero o más pares de argumentos y devuelve un objeto JSON bien formado que se compone de esos argumentos. El primer argumento de cada par es la etiqueta y el segundo argumento de cada par es el valor. Si algún argumento de json_object () es un BLOB, se produce un error.

La función json_object () actualmente permite etiquetas duplicadas sin quejas, aunque esto podría cambiar en una mejora futura.

Un argumento con el tipo de SQL TEXT normalmente se convierte en una cadena JSON entre comillas incluso si el texto de entrada es JSON bien formado. Sin embargo, si el argumento es el resultado directo de otra función json1, entonces se trata como JSON y se conserva toda su información y subestructura de tipo JSON. Esto permite anidar las llamadas a json_object () y json_array (). La función json () también se puede utilizar para forzar que las cadenas se reconozcan como JSON.

Ejemplos:

• objeto_json (‘a’, 2, ‘c’, 4)→ ‘”a”: 2, “c”: 4’
• json_object (‘a’, 2, ‘c’, ‘e: 5’)→ ‘”a”: 2, “c”: “e: 5″‘
• objeto_json (‘a’, 2, ‘c’, objeto_json (‘e’, 5))→ ‘”a”: 2, “c”: “e”: 5’

4.7. La función json_patch ()

La función SQL json_patch (T, P) ejecuta la RFC-7396 Algoritmo MergePatch para aplicar el parche P contra la entrada T. Se devuelve la copia parcheada de T.

MergePatch puede agregar, modificar o eliminar elementos de un objeto JSON, por lo que para los objetos JSON, la rutina json_patch () es un reemplazo generalizado de json_set () y json_remove (). Sin embargo, MergePatch trata los objetos JSON Array como atómicos. MergePatch no puede anexarse ​​a una matriz ni modificar elementos individuales de una matriz. Solo puede insertar, reemplazar o eliminar toda la matriz como una sola unidad. Por lo tanto, json_patch () no es tan útil cuando se trata de JSON que incluye matrices, especialmente matrices con mucha subestructura.

Ejemplos:

• json_patch (‘”a”: 1, “b”: 2’, ‘”c”: 3, “d”: 4’)→ ‘”a”: 1, “b”: 2, “c”: 3, “d”: 4’
• json_patch (‘”a”:[1,2], “b”: 2 ‘,’ “a”: 9 ‘)→ ‘”a”: 9, “b”: 2’
• json_patch (‘”a”:[1,2], “b”: 2 ‘,’ “a”: null ‘)→ ‘”b”: 2’
• json_patch (‘”a”: 1, “b”: 2’, ‘”a”: 9, “b”: null, “c”: 8’)→ ‘”a”: 9, “c”: 8’
• json_patch (‘”a”: “x”: 1, “y”: 2, “b”: 3’, ‘”a”: “y”: 9, “c”: 8 ‘)→ ‘”a”: “x”: 1, “y”: 9, “b”: 3, “c”: 8’

4.8. La función json_remove ()

La función json_remove (X, P, …) toma un solo valor JSON como primer argumento seguido de cero o más argumentos de ruta. La función json_remove (X, P, …) devuelve una copia del parámetro X con todos los elementos identificados por los argumentos de ruta eliminados. Las rutas que seleccionan elementos que no se encuentran en X se ignoran silenciosamente.

Las eliminaciones ocurren secuencialmente de izquierda a derecha. Los cambios causados ​​por eliminaciones anteriores pueden afectar la búsqueda de ruta de los argumentos posteriores.

Si se llama a la función json_remove (X) sin argumentos de ruta, devuelve la entrada X reformateada, con el exceso de espacio en blanco eliminado.

La función json_remove () arroja un error si el primer argumento no es JSON bien formado o si algún argumento posterior no es una ruta bien formada, o si algún argumento es un BLOB.

Ejemplos:

• json_remove (‘[0,1,2,3,4]PS[2]’)→ ‘[0,1,3,4]’
• json_remove (‘[0,1,2,3,4]PS[2]PS[0]’)→ ‘[1,3,4]’
• json_remove (‘[0,1,2,3,4]PS[0]PS[2]’)→ ‘[1,2,4]’
• json_remove (‘[0,1,2,3,4]PS[#-1]PS[0]’)→ ‘[1,2,3]’
• json_remove (‘”x”: 25, “y”: 42’)→ ‘”x”: 25, “y”: 42’
• json_remove (‘”x”: 25, “y”: 42’, ‘$. z’)→ ‘”x”: 25, “y”: 42’
• json_remove (‘”x”: 25, “y”: 42’, ‘$. y’)→ ‘”x”: 25’
• json_remove (‘”x”: 25, “y”: 42’, ‘$’)→ NULO

4.9. La función json_type ()

La función json_type (X) devuelve el “tipo” del elemento más externo de X. La función json_type (X, P) devuelve el “tipo” del elemento en X seleccionado por la ruta P. El “tipo” devuelto por json_type () es uno de los siguientes valores de texto SQL: ‘nulo’, ‘verdadero’, ‘falso’, ‘entero’, ‘real’, ‘texto’, ‘matriz’ u ‘objeto’. Si la ruta P en json_type (X, P) selecciona un elemento que no existe en X, esta función devuelve NULL.

La función json_type () arroja un error si alguno de sus argumentos no está bien formado o es un BLOB.

Ejemplos:

• json_type (‘”a”:[2,3.5,true,false,null,”x”] ‘)→ ‘objeto’
• json_type (‘{“a”:[2,3.5,true,false,null,”x”]PS→ ‘objeto’
• json_type (‘”a”:[2,3.5,true,false,null,”x”] ‘,’ $. a ‘)→ ‘matriz’
• json_type (‘”a”:[2,3.5,true,false,null,”x”] ‘,’ $. a[0]’)→ ‘entero’
• json_type (‘”a”:[2,3.5,true,false,null,”x”] ‘,’ $. a[1]’)→ ‘verdadero’
• json_type (‘”a”:[2,3.5,true,false,null,”x”] ‘,’ $. a[2]’)→ ‘verdadero’
• json_type (‘”a”:[2,3.5,true,false,null,”x”] ‘,’ $. a[3]’)→ ‘falso’
• json_type (‘”a”:[2,3.5,true,false,null,”x”] ‘,’ $. a[4]’)→ ‘nulo’
• json_type (‘”a”:[2,3.5,true,false,null,”x”] ‘,’ $. a[5]’)→ ‘texto’
• json_type (‘”a”:[2,3.5,true,false,null,”x”] ‘,’ $. a[6]’)→ NULO

4.10. La función json_valid ()

La función json_valid (X) devuelve 1 si el argumento X es JSON bien formado y devuelve 0 si el argumento X no es JSON bien formado.

Ejemplos:

• json_valid (‘”x”: 35’)→ 1
• json_valid (‘{“x”: 35’)→ 0

4.11. La función json_quote ()

La función json_quote (X) convierte el valor SQL X (un número o una cadena) en su representación JSON correspondiente.

Ejemplos:

• json_quote (3.14159)→ 3,14159
• json_quote (‘verde’)→ “verde”

4.12. Las funciones SQL agregadas json_group_array () y json_group_object ()

La función json_group_array (X) es una función SQL agregada que devuelve una matriz JSON compuesta por todos los valores X en la agregación. De manera similar, la función json_group_object (NAME, VALUE) devuelve un objeto JSON compuesto por todos los pares NAME / VALUE en la agregación.

4.13. Las funciones con valores de tabla json_each () y json_tree ()

Las funciones json_each (X) y json_tree (X) con valores de tabla recorren el valor JSON proporcionado como su primer argumento y devuelven una fila para cada elemento. La función json_each (X) solo recorre los hijos inmediatos de la matriz u objeto de nivel superior o solo el elemento de nivel superior en sí mismo si el elemento de nivel superior es un valor primitivo. La función json_tree (X) recorre recursivamente la subestructura JSON comenzando con el elemento de nivel superior.

Las funciones json_each (X, P) y json_tree (X, P) funcionan igual que sus contrapartes de un argumento, excepto que tratan el elemento identificado por la ruta P como el elemento de nivel superior.

El esquema de la tabla devuelto por json_each () y json_tree () es el siguiente:

CREATETABLE json_tree(keyANY,-- key for current element relative to its parentvalueANY,-- value for the current elementtypeTEXT,-- 'object','array','string','integer', etc.
    atom ANY,-- value for primitive types, null for array & object
    id INTEGER,-- integer ID for this element
    parent INTEGER,-- integer ID for the parent of this element
    fullkey TEXT,-- full path describing the current element
    path TEXT,-- path to the container of the current row
    json JSON HIDDEN,-- 1st input parameter: the raw JSON
    root TEXT HIDDEN     -- 2nd input parameter: the PATH at which to start);

La columna “clave” es el índice de matriz de números enteros para los elementos de una matriz JSON y la etiqueta de texto para los elementos de un objeto JSON. La columna de clave es NULL en todos los demás casos.

La columna “átomo” es el valor SQL correspondiente a elementos primitivos – elementos distintos de matrices y objetos JSON. La columna “átomo” es NULL para una matriz u objeto JSON. La columna “valor” es la misma que la columna “átomo” para elementos JSON primitivos, pero toma el valor JSON de texto para matrices y objetos.

La columna “tipo” es un valor de texto SQL tomado de (‘nulo’, ‘verdadero’, ‘falso’, ‘entero’, ‘real’, ‘texto’, ‘matriz’, ‘objeto’) según el tipo de el elemento JSON actual.

La columna “id” es un número entero que identifica un elemento JSON específico dentro de la cadena JSON completa. El entero “id” es un número de mantenimiento interno, cuyo cálculo podría cambiar en versiones futuras. La única garantía es que la columna “id” será diferente para cada fila.

La columna “principal” es siempre NULL para json_each (). Para json_tree (), la columna “parent” es el entero “id” para el elemento principal del elemento actual, o NULL para el elemento JSON de nivel superior o el elemento identificado por la ruta raíz en el segundo argumento.

La columna “clave completa” es una ruta de texto que identifica de forma única el elemento de fila actual dentro de la cadena JSON original. La clave completa del verdadero elemento de nivel superior se devuelve incluso si el argumento “raíz” proporciona un punto de partida alternativo.

La columna “ruta” es la ruta a la matriz o al contenedor de objetos que contiene la fila actual, o la ruta a la fila actual en el caso de que la iteración comience en un tipo primitivo y, por lo tanto, solo proporcione una única fila de salida.

4.13.1. Ejemplos usando json_each () y json_tree ()

Suponga que la tabla “CREATE TABLE user (name, phone)” almacena cero o más números de teléfono como un objeto de matriz JSON en el campo user.phone. Para encontrar a todos los usuarios que tienen cualquier número de teléfono con un código de área 704:

SELECTDISTINCTuser.name
  FROMuser, json_each(user.phone)WHERE json_each.valueLIKE'704-%';

Ahora suponga que el campo user.phone contiene texto sin formato si el usuario tiene un solo número de teléfono y una matriz JSON si el usuario tiene varios números de teléfono. Se plantea la misma pregunta: “¿Qué usuarios tienen un número de teléfono en el código de área 704?” Pero ahora la función json_each () solo se puede llamar para aquellos usuarios que tienen dos o más números de teléfono, ya que json_each () requiere JSON bien formado como primer argumento:

SELECT name FROMuserWHERE phone LIKE'704-%'UNIONSELECTuser.name
  FROMuser, json_each(user.phone)WHERE json_valid(user.phone)AND json_each.valueLIKE'704-%';

Considere una base de datos diferente con “CREATE TABLE big (json JSON)”. Para ver una descomposición completa línea por línea de los datos:

SELECT big.rowid, fullkey,valueFROM big, json_tree(big.json)WHERE json_tree.typeNOTIN('object','array');

En el anterior, el término “tipo NOT IN (‘objeto’, ‘matriz’)” de la cláusula WHERE suprime los contenedores y solo deja pasar los elementos hoja. El mismo efecto podría lograrse de esta manera:

SELECT big.rowid, fullkey, atom
  FROM big, json_tree(big.json)WHERE atom ISNOTNULL;

Suponga que cada entrada en la tabla BIG es un objeto JSON con un campo ‘$ .id’ que es un identificador único y un campo ‘$ .partlist’ que puede ser un objeto profundamente anidado. Desea encontrar la identificación de cada entrada que contenga una o más referencias a uuid ‘6fa5181e-5721-11e5-a04e-57f3d7b32808’ en cualquier lugar de su ‘$ .partlist’.

SELECTDISTINCT json_extract(big.json,'$.id')FROM big, json_tree(big.json,'$.partlist')WHERE json_tree.key='uuid'AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';