Categorias:

Funções de dados semiestruturados e estruturados (Matriz/objeto)

OBJECT_INSERT

Retorna um valor OBJECT que consiste no valor OBJECT de entrada com um novo par chave-valor inserido (ou uma chave existente atualizada com um novo valor).

Sintaxe

OBJECT_INSERT( <object> , <key> , <value> [ , <updateFlag> ] )
Copy

Argumentos

Obrigatório:

object

A valor OBJECT de origem no qual o novo par chave-valor é inserido ou no qual um par chave-valor existente é atualizado.

key

A nova chave a ser inserida no valor OBJECT ou uma chave existente cujo valor está sendo atualizado. A chave especificada deve ser diferente de todas as chaves existentes no valor OBJECT, a menos que updateFlag esteja definido como TRUE.

value

O valor associado à chave.

Opcional:

updateFlag

Um sinalizador booliano que, quando definido como TRUE, especifica que o valor de entrada atualiza o valor de uma chave existente no valor OBJECT, em vez de inserir um novo par chave-valor.

O padrão é FALSE.

Retornos

Esta função retorna um valor que tem o tipo de dados OBJECT.

Notas de uso

  • A função é compatível com os valores JSON nulos, mas não com os valores ou chaves SQL NULL:

    • Se key for qualquer cadeia de caracteres diferente de NULL e value for um JSON nulo (por exemplo, PARSE_JSON('null')), o par chave-valor será inserido no valor OBJECT retornado.

    • Se key ou value for um SQL NULL, o par chave-valor será omitido do valor OBJECT retornado.

  • Se o argumento opcional updateFlag for definido como TRUE, a entrada existente key será atualizada para a entrada value. Se updateFlag for omitido ou definido como FALSE, chamar essa função com uma chave de entrada que já existe no valor OBJECT resultará em um erro.

  • Se o sinalizador de atualização estiver definido como TRUE, mas a chave correspondente ainda não existir no valor OBJECT, o par chave-valor será adicionado.

  • Para valores OBJECT estruturados:

    • Para os argumentos que são chaves, você deve especificar constantes.

    • Quando o argumento updateFlag é FALSE (quando você está inserindo um novo par chave-valor):

      • Se você especificar uma chave que já exista no valor OBJECT, ocorrerá um erro.

        SELECT OBJECT_INSERT(
          {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
          'city',
          'San Jose',
          false
        );
        
        Copy
        093202 (23001): Function OBJECT_INSERT:
          expected structured object to not contain field city but it did.
        
      • A função retorna um valor OBJECT estruturado. O tipo do valor OBJECT inclui a chave recém-inserida. Por exemplo, suponha que você adicione a chave zipcode com o valor FLOAT 94402:

        SELECT
          OBJECT_INSERT(
            {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
            'zip_code',
            94402::FLOAT,
            false
          ) AS new_object,
          SYSTEM$TYPEOF(new_object) AS type;
        
        Copy
        +-------------------------------------+---------------------------------------------------------------------------------------+
        | NEW_OBJECT                          | TYPE                                                                                  |
        |-------------------------------------+---------------------------------------------------------------------------------------|
        | {                                   | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216), zip_code FLOAT NOT NULL)[LOB] |
        |   "city": "San Mateo",              |                                                                                       |
        |   "state": "CA",                    |                                                                                       |
        |   "zip_code": 9.440200000000000e+04 |                                                                                       |
        | }                                   |                                                                                       |
        +-------------------------------------+---------------------------------------------------------------------------------------+
        

        O tipo do valor inserido determina o tipo adicionado à definição de tipo OBJECT. Nesse caso, o valor de zipcode é um valor convertido em FLOAT, portanto, o tipo de zipcode é FLOAT.

    • Quando o argumento updateFlag é TRUE (quando você está substituindo um par chave-valor existente):

      • Se você especificar uma chave que não existe no valor OBJECT, ocorrerá um erro.

      • A função retorna um valor OBJECT estruturado do mesmo tipo.

      • O tipo do valor inserido é imposto ao tipo da chave existente.

Exemplos

Os exemplos utilizam a seguinte tabela:

CREATE OR REPLACE TABLE object_insert_examples (object_column OBJECT);

INSERT INTO object_insert_examples (object_column)
  SELECT OBJECT_CONSTRUCT('a', 'value1', 'b', 'value2');

SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2"  |
| }                |
+------------------+

Como adicionar um novo par chave-valor a um valor OBJECT

Insira um terceiro par chave-valor em um valor OBJECT que tenha dois pares chave-valor:

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'c', 'value3');

SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2", |
|   "c": "value3"  |
| }                |
+------------------+

Insira dois novos pares chave-valor no valor OBJECT, omitindo um par chave-valor:

  • d consiste em um valor JSON nulo.

  • e consiste de um valor SQL NULL e é, portanto, omitido.

  • f consiste em uma cadeia de caracteres contendo “nulo”.

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'd', PARSE_JSON('null'));

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'e', NULL);

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'f', 'null');

SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+

Atualização de um par chave-valor em um valor OBJECT

Atualize um par chave-valor existente ("b": "value2") no valor OBJECT com um novo valor ("valuex"):

UPDATE object_insert_examples
  SET object_column = OBJECT_INSERT(object_column, 'b', 'valuex', TRUE);

SELECT * FROM object_insert_examples;
Copy
+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "valuex", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+