:Funciones JSON

• JSON_ARRAY_CONTAINS: Comprueba si un arreglo JSON contiene el value especificado.

• JSON_ARRAY_LENGTH: Devuelve la longitud de un arreglo JSON.

• JSON_CONTAINS (para versiones 3.1.5.0 y posteriores): Comprueba si la ruta especificada contiene el valor candidate. Si no se especifica ninguna ruta, esta función comprueba si el destino contiene el valor candidate.

• JSON_CONTAINS_PATH (para versiones 3.1.5.0 y posteriores): Comprueba si un documento JSON contiene alguna o todas las rutas especificadas.

• JSON_EXTRACT: Extrae datos de un documento JSON en la ruta json_path especificada.

• JSON_KEYS: Si se especifica json_path, esta función devuelve todas las claves de la ruta indicada de un documento JSON. Si no se especifica json_path, la función devuelve todas las claves de la ruta raíz (json_path='$').

• JSON_OVERLAPS (para versiones 3.1.10.6 y posteriores): Comprueba si un documento JSON contiene alguno de los elementos especificados, como candidate1, candidate2 o candidate3.

• JSON_REMOVE (para versiones 3.1.10.0 y posteriores): Elimina el elemento en la ruta json_path especificada de un documento json y devuelve la cadena modificada. Puede especificar varios elementos para eliminar mediante array[json_path,json_path,...].

• JSON_SIZE: Devuelve el tamaño del objeto o arreglo JSON en la ruta json_path especificada.

• JSON_SET (para versiones 3.2.2.8 y posteriores): Inserta o actualiza datos en un documento json en la ruta json_path especificada y devuelve el documento json actualizado.

• JSON_UNQUOTE (versiones 3.2.2.11 y posteriores): Elimina las comillas dobles de json_value, quita caracteres de escape específicos en json_value y devuelve el valor resultante.

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

• Descripción: Comprueba si un arreglo JSON contiene el value especificado.

• Tipo de valor de entrada: value puede ser de tipo numérico, cadena o BOOLEAN.

• Tipo de valor devuelto: BOOLEAN.

• Ejemplo:

  • Compruebe si el arreglo JSON [1, 2, 3] contiene el elemento 2. La sentencia es la siguiente:

    SELECT json_array_contains('[1, 2, 3]', 2);

    Se devuelve el siguiente resultado:

    +-------------------------------------+
    | json_array_contains('[1, 2, 3]', 2) |
    +-------------------------------------+
    |                1                    |
    +-------------------------------------+

JSON_ARRAY_LENGTH

json_array_length(json)

• Descripción: Devuelve la longitud de un arreglo JSON.

• Tipo de valor de entrada: Cadena o JSON.

• Tipo de valor devuelto: BIGINT.

• Ejemplo:

  • Obtenga la longitud del arreglo JSON [1, 2, 3]. La sentencia es la siguiente:

    SELECT json_array_length('[1, 2, 3]');

    Se devuelve el siguiente resultado:

    +--------------------------------+
    | json_array_length('[1, 2, 3]') |
    +--------------------------------+
    |                 3              |
    +--------------------------------+

JSON_CONTAINS

La función JSON_CONTAINS comprueba si un documento JSON especificado contiene un valor específico. Puede usar un índice de arreglo JSON en sus consultas para evitar escaneos completos de tabla o el análisis sintáctico de todo el documento JSON, lo que mejora la eficiencia de las consultas.

json_contains(target, candidate[, json_path])

json_contains(json_path, cast('[candidate1,candidate2,candidate3]' as json)) 

JSON_CONTAINS_PATH

json_contains_path(json, one_or_all, json_path[, json_path,...])

• Descripción del comando: Comprueba si la ruta especificada existe en el objeto JSON.

  • Si one_or_all se establece en 'one', la función devuelve 1 si el documento JSON contiene alguna de las rutas especificadas. De lo contrario, devuelve 0.

  • Si one_or_all se establece en 'all', la función devuelve 1 si el documento JSON contiene todas las rutas especificadas. De lo contrario, devuelve 0.

• Tipo de valor de entrada: json es de tipo JSON. one_or_all es de tipo VARCHAR y puede ser 'one' o 'all' (sin distinción entre mayúsculas y minúsculas). json_path es una expresión de ruta.

• Tipo de valor devuelto: BOOLEAN.

• Ejemplos:

  • Compruebe si el documento JSON contiene al menos una de las rutas $.a y $.e. La sentencia es la siguiente:

    SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') AS RESULT;

    Se devuelve el siguiente resultado:

    +--------+
    | result |
    +--------+
    |      1 |
    +--------+

  • Compruebe si el documento JSON contiene ambas rutas $.a y $.e. La sentencia es la siguiente:

    SELECT json_contains_path(json '{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') AS RESULT;

    Se devuelve el siguiente resultado:

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_EXTRACT

json_extract(json, json_path)

• Descripción: Extrae un valor de un documento JSON en la ruta json_path especificada. Si una clave en el documento json contiene caracteres especiales, como $ o ., el formato de json_path debe ser '$["Key"]'.

  Por ejemplo, si la clave es $data, json_path debe ser '$["$data"]'.

• Tipo de valor de entrada: Cadena o JSON.

• Tipo de valor devuelto: JSON.

• Ejemplos:

  • Obtenga el valor en la ruta $[0] del arreglo [10, 20, [30, 40]]. La sentencia es la siguiente:

    SELECT json_extract('[10, 20, [30, 40]]', '$[0]');

    Se devuelve el siguiente resultado:

    +-------------------------------------------+
    | json_extract('[10, 20, [30, 40]]', '$[0]') |
    +-------------------------------------------+
    |                     10                    |
    +-------------------------------------------+

  • Obtenga el valor de la ruta $date de {"id":"1","$date":"12345"}. La sentencia es la siguiente:

    SELECT JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]');

    Se devuelve el siguiente resultado:

    +---------------------------------------------------------+
    |JSON_EXTRACT('{"id":"1","$date":"12345"}', '$["$date"]') |
    +---------------------------------------------------------+
    |                       "12345"                           |
    +---------------------------------------------------------+

JSON_KEYS

json_keys(json[, json_path])

• Descripción

  • Si se especifica json_path, esta función devuelve todas las claves de la ruta indicada del documento JSON.

  • Si no se especifica json_path, esta función devuelve todas las claves de la ruta raíz (json_path='$').

• Tipo de valor de entrada: Solo se admiten parámetros de tipo JSON.

  Puede construir datos JSON de las siguientes maneras:

  • Use datos JSON directamente. Por ejemplo, json '{"a": 1, "b": {"c": 30}}'.

  • Convierta explícitamente una cadena a datos JSON mediante la función CAST. Por ejemplo, CAST('{"a": 1, "b": {"c": 30}}' AS json).

• Tipo de valor devuelto: JSON ARRAY.

• Ejemplos:

  • Obtenga todas las claves de la ruta $.b. La sentencia es la siguiente:

    SELECT json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b');

    Se devuelve el siguiente resultado:

    +-----------------------------------------------------------+
    | json_keys(CAST('{"a": 1, "b": {"c": 30}}' AS json),'$.b') |
    +-----------------------------------------------------------+
    |                           ["c"]                           |
    +-----------------------------------------------------------+

  • Obtenga todas las claves de la ruta raíz. La sentencia es la siguiente:

    SELECT JSON_KEYS(json '{"a": 1, "b": {"c": 30}}');

    Se devuelve el siguiente resultado:

    +--------------------------------------------+
    | JSON_KEYS(json '{"a": 1, "b": {"c": 30}}') |
    +--------------------------------------------+
    |             ["a","b"]                      |
    +--------------------------------------------+

JSON_OVERLAPS

json_overlaps(json, cast('[candidate1,candidate2,candidate]' as json)) 

• Descripción: Comprueba si el documento JSON especificado contiene alguno de los elementos indicados, como candidate1, candidate2 o candidate3.

• Tipos de datos de los valores de entrada: candidate1,candidate2,candidate3,... pueden ser de tipo numérico o cadena, y todos los valores deben tener el mismo tipo de dato.

• Tipo de valor devuelto: VARCHAR.

• Ejemplos:

  • Obtenga datos de la columna JSON especificada vj que contengan CP-018673.

    SELECT * FROM json_test WHERE json_overlaps(vj, cast('["CP-018673"]' AS json));

    Se devuelve el siguiente resultado:

    +-----+----------------------------------------------------------------------------+
    |  id |   vj                                                                       |
    +-----+----------------------------------------------------------------------------+
    |  2  | ["CP-018673", 1, false]                                                    |
    +-----+----------------------------------------------------------------------------+
    |  3  | ["CP-018673", 1, false, {"a": 1}]                                          |
    +-----+----------------------------------------------------------------------------+
    |  5  | ["CP-018673","CP-018671","CP-018672","CP-018670","CP-018669","CP-018668"]  |
    +-----+----------------------------------------------------------------------------+

  • Obtenga datos de la columna JSON especificada vj que contengan cualquiera de los elementos 1, 2 o 3.

    SELECT * FROM json_test WHERE json_overlaps(vj, cast('[1,2,3]' AS json))

    Se devuelve el siguiente resultado:

    +-----+-------------------------------------+
    |  id |                 vj                  |
    +-----+-------------------------------------+
    |  1  | [1,2,3]                             |
    +-----+-------------------------------------+
    |  2  | ["CP-018673", 1, false]             |
    +-----+-------------------------------------+
    |  3  | ["CP-018673", 1, false, {"a": 1}]   |
    +-----+-------------------------------------+

JSON_REMOVE

json_remove(json,json_path)
json_remove(json,array[json_path,json_path,...])

• Descripción: Elimina el elemento en la ruta json_path especificada de un documento json y devuelve la cadena modificada. Puede especificar varios elementos para eliminar mediante array[json_path,json_path,...].

• Tipo de valor de entrada: json es una cadena VARCHAR en formato JSON. json_path es una cadena VARCHAR en formato JSON.

• Tipo de valor devuelto: VARCHAR.

• Ejemplos

  • Elimine el elemento en la ruta $.glossary.GlossDiv y obtenga la cadena modificada. La sentencia es la siguiente:

      SELECT json_remove(
      '{
          "glossary": {
              "title": "example glossary",
              "GlossDiv": {
                  "title": "S",
                  "GlossList": {
                      "GlossEntry": {
                          "ID": "SGML",
                          "SortAs": "SGML",
                          "GlossTerm": "Standard Generalized Markup Language",
                          "Acronym": "SGML",
                          "Abbrev": "ISO 8879:1986",
                          "GlossDef": {
                              "para": "A meta-markup language, used to create markup languages such as DocBook.",
                              "GlossSeeAlso": ["GML", "XML"]
                          },
                          "GlossSee": "markup"
                      }
                  }
              }
          }
      }'
      , '$.glossary.GlossDiv') a;

    Se devuelve el siguiente resultado:

    {"glossary":{"title":"example glossary"}}

  • Elimine los elementos en las rutas $.glossary.title y $.glossary.GlossDiv.title y obtenga la cadena modificada. La sentencia es la siguiente:

    SELECT json_remove(
      '{
          "glossary": {
              "title": "example glossary",
              "GlossDiv": {
                  "title": "S",
                  "GlossList": {
                      "GlossEntry": {
                          "ID": "SGML",
                          "SortAs": "SGML",
                          "GlossTerm": "Standard Generalized Markup Language",
                          "Acronym": "SGML",
                          "Abbrev": "ISO 8879:1986",
                          "GlossDef": {
                              "para": "A meta-markup language, used to create markup languages such as DocBook.",
                              "GlossSeeAlso": ["GML", "XML"]
                          },
                          "GlossSee": "markup"
                      }
                  }
              }
          }
      }'
      , array['$.glossary.title', '$.glossary.GlossDiv.title']) a;

    Se devuelve el siguiente resultado:

    {"glossary":{"GlossDiv":{"GlossList":{"GlossEntry":{"GlossTerm":"Standard Generalized Markup Language","GlossSee":"markup","SortAs":"SGML","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"ID":"SGML","Acronym":"SGML","Abbrev":"ISO 8879:1986"}}}}}

JSON_SIZE

json_size(json, json_path)

• Descripción: Devuelve el tamaño de un objeto o arreglo JSON en la ruta json_path especificada.

  Nota

  Si json_path no apunta a un objeto o arreglo JSON, esta función devuelve 0.

• Tipo de valor de entrada: Cadena o JSON.

• Tipo de valor devuelto: BIGINT.

• Ejemplos:

  • json_path apunta a un objeto JSON. La sentencia es la siguiente:

    SELECT json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;

    Se devuelve el siguiente resultado:

    +--------+
    | result |
    +--------+
    |      2 |
    +--------+

  • json_path no apunta a un objeto o arreglo JSON. La sentencia es la siguiente:

    SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result;

    Se devuelve el siguiente resultado:

    +--------+
    | result |
    +--------+
    |      0 |
    +--------+

JSON_SET

json_set(json, json_path, value[, json_path, value] ...)

• Descripción: Inserta o actualiza datos en un documento json en la ruta json_path especificada y devuelve el documento json actualizado.

  • Si json o json_path es null, la función devuelve null.

  • Si el documento json no tiene un formato JSON válido, o si alguna ruta json_path no es una expresión de ruta válida, se genera una excepción.

  • Si la ruta json_path especificada existe, su valor se sobrescribe con value.

  • Si la ruta json_path especificada no existe en el documento json:

    • Si json_path apunta a un objeto JSON, value se agrega como un nuevo elemento en la ubicación especificada por json_path.

    • Si json_path apunta a un arreglo JSON, esta función comprueba si existen datos en la posición anterior a la ruta json_path especificada. Si no existen datos, se agregan valores null para llenar el espacio antes de insertar value. De lo contrario, value se inserta directamente.

    • En otros casos, se genera una excepción.

• Tipos de valores de entrada:

  • json: VARCHAR o JSON.

  • json_path: VARCHAR.

  • value: BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, VARCHAR, VARBINARY, DATE, DATETIME, TIMESTAMP o TIME.

• Tipo de valor devuelto: JSON.

• Ejemplos:

  • Insertar datos en un documento json donde json_path es null.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', null, '10');

    Resultado:

    +------------------------------------------------+
    | JSON_SET('{ "a": 1, "b": [2, 3]}', NULL, '10') |
    +------------------------------------------------+
    | null                                           |
    +------------------------------------------------+

  • Insertar datos en un documento json donde json_path no es una expresión de ruta válida.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b.c', '10');

    Resultado:

    Failed to execute json_set() for json_path: $.b.c

  • Insertar datos en un documento json donde json_path1 existe, y json_path2 no existe y apunta a un objeto JSON.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');

    Resultado:

    +-----------------------------------------------------------------------+
    | JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------------------------+
    | {"a":10,"b":[2,3],"c":"[true, false]"}                                |
    +-----------------------------------------------------------------------+

  • Insertar datos en un documento json donde la ruta json_path especificada no existe y apunta a un arreglo JSON.

    SELECT JSON_SET('{ "a": 1, "b": [2, 3]}',  '$.b[4]', '[true, false]');

    Resultado:

    +----------------------------------------------------------------+
    | JSON_SET('{ "a": 1, "b": [2, 3]}',  '$.b[4]', '[true, false]') |
    +----------------------------------------------------------------+
    | {"a":1,"b":[2,3,null,null,"[true, false]"]}                    |
    +----------------------------------------------------------------+

JSON_UNQUOTE

json_unquote(json_value)

• Este comando elimina las comillas dobles de json_value, procesa ciertos caracteres de escape y devuelve el valor resultante.

  AnalyticDB for MySQL no valida json_value. Esta función procesa el valor según la lógica descrita, independientemente de si json_value cumple con la sintaxis JSON.

  Los caracteres de escape compatibles se enumeran en la siguiente tabla.

  Antes de eliminar el escape	Después de eliminar el escape
  \"	Comilla doble (").
  \b	Retroceso.
  \f	Salto de formulario.
  \n	Salto de línea.
  \r	Retorno de carro.
  \t	Tabulación.
  \\	Barra invertida (\).
  \uXXXX	Representación de carácter UTF-8.

• Tipo de valor de entrada: VARCHAR.

• Tipo de valor devuelto: VARCHAR.

• Ejemplos:

  • Devolver la cadena sin comillas abc. La sentencia es la siguiente:

    SELECT json_unquote('"abc"');

    Se devuelve el siguiente resultado:

    +-----------------------+
    | json_unquote('"abc"') |
    +-----------------------+
    |          abc          |
    +-----------------------+

  • La siguiente sentencia devuelve la cadena sin comillas y procesada:

    SELECT json_unquote('"\\t\\u0032"');

    El resultado es el siguiente:

    +------------------------------+
    | json_unquote('"\\t\\u0032"') |
    +------------------------------+
    |               2              |
    +------------------------------+

Apéndice: Sintaxis de JSON Path

Uso

• Utilice $.keyName[.keyName]... para acceder a una clave específica en un objeto JSON.

• Utilice $[nonNegativeInteger] para acceder al enésimo elemento de un arreglo JSON, donde n es un entero no negativo.

• Utilice $.keyName[.keyName]...[nonNegativeInteger] para acceder al enésimo elemento de un arreglo JSON anidado en un objeto JSON, donde n es un entero no negativo.

Notas

La sintaxis de JSON Path en AnalyticDB for MySQL no admite los caracteres comodín * y **. Esto significa que no se admiten expresiones como '$.*', '$.hobbies[*]', '$.address.**' y '$.hobbies.**'.

Ejemplos

Suponga que tiene los siguientes datos JSON.

{
    "name": "Alice",
    "age": 25,
    "address": {
        "city": "Hangzhou",
        "zip": "10001"
    },
    "hobbies":["reading", "swimming", "cycling"]
}

Preguntas frecuentes

Referencias

• JSON: Describe el tipo de datos JSON.

• JSON indexes: Describe cómo crear índices para objetos y arreglos JSON.