AnalyticDB:Funções JSON

• JSON_ARRAY_CONTAINS: Verifica se um array JSON contém o value especificado.

• JSON_ARRAY_LENGTH: Retorna o comprimento de um array JSON.

• JSON_CONTAINS (para versões 3.1.5.0 e posteriores): Verifica se o caminho especificado contém o valor candidate. Se nenhum caminho for especificado, a função verifica se o destino contém o valor candidate.

• JSON_CONTAINS_PATH (para versões 3.1.5.0 e posteriores): Verifica se um documento JSON contém algum ou todos os caminhos especificados.

• JSON_EXTRACT: Extrai dados de um documento JSON no json_path especificado.

• JSON_KEYS: Se json_path for especificado, retorna todas as chaves do caminho indicado em um documento JSON. Caso json_path não seja especificado, retorna todas as chaves do caminho raiz (json_path='$').

• JSON_OVERLAPS (para versões 3.1.10.6 e posteriores): Verifica se um documento JSON contém qualquer um dos elementos especificados, como candidate1, candidate2 ou candidate3.

• JSON_REMOVE (para versões 3.1.10.0 e posteriores): Remove o elemento no json_path especificado de um documento json e retorna a string modificada. Use array[json_path,json_path,...] para especificar vários elementos para remoção.

• JSON_SIZE: Retorna o tamanho do objeto ou array JSON no json_path especificado.

• JSON_SET (para versões 3.2.2.8 e posteriores): Insere ou atualiza dados em um documento json no json_path especificado e retorna o documento json atualizado.

• JSON_UNQUOTE (versões 3.2.2.11 e posteriores): Remove as aspas duplas de json_value, processa caracteres de escape específicos em json_value e retorna o valor resultante.

JSON_ARRAY_CONTAINS

json_array_contains(json, value)

• Descrição: Verifica se um array JSON contém o value especificado.

• Tipo de valor de entrada: value pode ser numérico, string ou BOOLEAN.

• Tipo de valor de retorno: BOOLEAN.

• Exemplo:

  • Verifique se o array JSON [1, 2, 3] contém o elemento 2. Instrução:

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

    Resultado retornado:

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

JSON_ARRAY_LENGTH

json_array_length(json)

• Descrição: Retorna o comprimento de um array JSON.

• Tipo de valor de entrada: String ou JSON.

• Tipo de valor de retorno: BIGINT.

• Exemplo:

  • Obtenha o comprimento do array JSON [1, 2, 3]. Instrução:

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

    Resultado retornado:

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

JSON_CONTAINS

A função JSON_CONTAINS verifica se um documento JSON especificado contém um valor específico. O uso de um índice de array JSON nas consultas evita varreduras completas de tabela ou a análise de todo o documento JSON, melhorando a eficiência da consulta.

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,...])

• Descrição do comando: Verifica se o caminho especificado existe no objeto JSON.

  • Se one_or_all estiver definido como 'one', a função retorna 1 se o documento JSON contiver qualquer um dos caminhos especificados. Caso contrário, retorna 0.

  • Se one_or_all estiver definido como 'all', a função retorna 1 se o documento JSON contiver todos os caminhos especificados. Caso contrário, retorna 0.

• Tipo de valor de entrada: json é do tipo JSON. one_or_all é do tipo VARCHAR e pode ser 'one' ou 'all' (não diferencia maiúsculas de minúsculas). json_path é uma expressão de caminho.

• Tipo de valor de retorno: BOOLEAN.

• Exemplos:

  • Verifique se o documento JSON contém pelo menos um dos caminhos $.a e $.e. Instrução:

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

    Resultado retornado:

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

  • Verifique se o documento JSON contém ambos os caminhos $.a e $.e. Instrução:

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

    Resultado retornado:

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

JSON_EXTRACT

json_extract(json, json_path)

• Descrição: Extrai um valor de um documento JSON no json_path especificado. Se uma chave no documento json contiver caracteres especiais, como $ ou ., o formato de json_path deve ser '$["Key"]'.

  Por exemplo, se a chave for $data, json_path deve ser '$["$data"]'.

• Tipo de valor de entrada: String ou JSON.

• Tipo de valor de retorno: JSON.

• Exemplos:

  • Obtenha o valor no caminho $[0] do array [10, 20, [30, 40]]. Instrução:

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

    Resultado retornado:

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

  • Obtenha o valor do caminho $date de {"id":"1","$date":"12345"}. Instrução:

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

    Resultado retornado:

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

JSON_KEYS

json_keys(json[, json_path])

• Descrição

  • Se json_path for especificado, a função retorna todas as chaves do caminho indicado no documento JSON.

  • Se json_path não for especificado, a função retorna todas as chaves do caminho raiz (json_path='$').

• Tipo de valor de entrada: Apenas parâmetros do tipo JSON são suportados.

  Construa dados JSON das seguintes maneiras:

  • Use dados JSON diretamente. Por exemplo, json '{"a": 1, "b": {"c": 30}}'.

  • Converta explicitamente uma string para dados JSON usando a função CAST. Por exemplo, CAST('{"a": 1, "b": {"c": 30}}' AS json).

• Tipo de valor de retorno: JSON ARRAY.

• Exemplos:

  • Obtenha todas as chaves do caminho $.b. Instrução:

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

    Resultado retornado:

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

  • Obtenha todas as chaves do caminho raiz. Instrução:

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

    Resultado retornado:

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

JSON_OVERLAPS

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

• Descrição: Verifica se o documento JSON especificado contém qualquer um dos elementos indicados, como candidate1, candidate2 ou candidate3.

• Tipos de dados dos valores de entrada: candidate1,candidate2,candidate3,... podem ser do tipo numérico ou string, e todos os valores devem ter o mesmo tipo de dado.

• Tipo de valor de retorno: VARCHAR.

• Exemplos:

  • Retorne dados da coluna JSON especificada vj que contenham CP-018673.

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

    Resultado retornado:

    +-----+----------------------------------------------------------------------------+
    |  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"]  |
    +-----+----------------------------------------------------------------------------+

  • Retorne dados da coluna JSON especificada vj que contenham qualquer um dos elementos 1, 2 ou 3.

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

    Resultado retornado:

    +-----+-------------------------------------+
    |  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,...])

• Descrição: Remove o elemento no json_path especificado de um documento json e retorna a string modificada. Use array[json_path,json_path,...] para especificar vários elementos para remoção.

• Tipo de valor de entrada: json é uma string VARCHAR no formato JSON. json_path é uma string VARCHAR no formato JSON.

• Tipo de valor de retorno: VARCHAR.

• Exemplos

  • Remova o elemento no caminho $.glossary.GlossDiv e obtenha a string modificada. Instrução:

      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;

    Resultado retornado:

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

  • Remova os elementos nos caminhos $.glossary.title e $.glossary.GlossDiv.title e obtenha a string modificada. Instrução:

    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;

    Resultado retornado:

    {"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)

• Descrição: Retorna o tamanho de um objeto ou array JSON no json_path especificado.

  Nota

  Se json_path não apontar para um objeto ou array JSON, esta função retorna 0.

• Tipo de valor de entrada: String ou JSON.

• Tipo de valor de retorno: BIGINT.

• Exemplos:

  • json_path aponta para um objeto JSON. Instrução:

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

    Resultado retornado:

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

  • json_path não aponta para um objeto ou array JSON. Instrução:

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

    Resultado retornado:

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

JSON_SET

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

• Descrição: Insere ou atualiza dados em um documento json no json_path especificado e retorna o documento json atualizado.

  • Se json ou json_path for nulo, a função retorna nulo.

  • Se o documento json não estiver em um formato JSON válido, ou se algum json_path não for uma expressão de caminho válida, uma exceção será lançada.

  • Se o json_path especificado existir, seu valor será sobrescrito por value.

  • Se o json_path especificado não existir no documento json:

    • Se json_path apontar para um objeto JSON, value será adicionado como um novo elemento na localização especificada por json_path.

    • Se json_path apontar para um array JSON, esta função verifica se existem dados na posição anterior ao json_path especificado. Se não houver dados, valores null serão adicionados para preencher a lacuna antes que value seja inserido. Caso contrário, value será inserido diretamente.

    • Em outros casos, uma exceção será lançada.

• Tipos de valores de entrada:

  • json: VARCHAR ou JSON.

  • json_path: VARCHAR.

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

• Tipo de valor de retorno: JSON.

• Exemplos:

  • Insira dados em um documento json onde json_path é nulo.

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

    Resultado:

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

  • Insira dados em um documento json onde json_path não é uma expressão de caminho válida.

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

    Resultado:

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

  • Insira dados em um documento json onde json_path1 existe, e json_path2 não existe e aponta para um 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]"}                                |
    +-----------------------------------------------------------------------+

  • Insira dados em um documento json onde o json_path especificado não existe e aponta para um array 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 remove as aspas duplas de json_value, processa certos caracteres de escape e retorna o valor resultante.

  O AnalyticDB for MySQL não valida json_value. Esta função processa o valor com base na lógica descrita, independentemente de json_value estar em conformidade com a sintaxe JSON.

  Os caracteres de escape suportados estão listados na tabela a seguir.

  Antes de remover escape	Após remover escape
  \"	Aspas duplas (").
  \b	Backspace.
  \f	Form feed.
  \n	Line feed.
  \r	Carriage return.
  \t	Tabulação.
  \\	Barra invertida (\).
  \uXXXX	Representação de caractere UTF-8.

• Tipo de valor de entrada: VARCHAR.

• Tipo de valor de retorno: VARCHAR.

• Exemplos:

  • Obtenha a string sem aspas abc. Instrução:

    SELECT json_unquote('"abc"');

    Resultado retornado:

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

  • A seguinte instrução retorna a string sem aspas e analisada:

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

    Resultado:

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

Apêndice: Sintaxe de JSON Path

Uso

• Use $.keyName[.keyName]... para acessar uma chave específica em um objeto JSON.

• Use $[nonNegativeInteger] para acessar o enésimo elemento em um array JSON, onde n é um inteiro não negativo.

• Use $.keyName[.keyName]...[nonNegativeInteger] para acessar o enésimo elemento de um array JSON aninhado em um objeto JSON, onde n é um inteiro não negativo.

Observações

A sintaxe JSON Path no AnalyticDB for MySQL não oferece suporte aos caracteres curinga * e **. Isso significa que expressões como '$.*', '$.hobbies[*]', '$.address.**' e '$.hobbies.**' não são suportadas.

Exemplos

Suponha que você tenha os seguintes dados JSON.

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

Perguntas frequentes

Referências

• JSON: Descreve o tipo de dado JSON.

• Índices JSON: Descreve como criar índices para objetos e arrays JSON.