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

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
);

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 VARCHAR 94402:

SELECT
  OBJECT_INSERT(
    {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
    'zip_code',
    94402::VARCHAR,
    false
  ) AS new_object,
  SYSTEM$TYPEOF(new_object) AS type;

+------------------------+---------------------------------------------------------------------+
| NEW_OBJECT             | TYPE                                                                |
|------------------------+---------------------------------------------------------------------|
| {                      | OBJECT(city VARCHAR, state VARCHAR, zip_code VARCHAR NOT NULL)[LOB] |
|   "city": "San Mateo", |                                                                     |
|   "state": "CA",       |                                                                     |
|   "zip_code": "94402"  |                                                                     |
| }                      |                                                                     |
+------------------------+---------------------------------------------------------------------+

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

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 a seguir chamam a função OBJECT_INSERT:

Adicionar e atualizar pares chave-valor¶

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;

+------------------+
| 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;

+------------------+
| 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;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "value2", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+

Atualizar 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;

+------------------+
| OBJECT_COLUMN    |
|------------------|
| {                |
|   "a": "value1", |
|   "b": "valuex", |
|   "c": "value3", |
|   "d": null,     |
|   "f": "null"    |
| }                |
+------------------+

Adicionar e atualizar valores OBJECT aninhados¶

Os exemplos usam a tabela a seguir com valores OBJECT aninhados:

CREATE OR REPLACE TABLE sample_nested_object (
  id INTEGER,
  nested_object OBJECT);

INSERT INTO sample_nested_object (id, nested_object)
  SELECT 1,
         OBJECT_CONSTRUCT(
           'outer_key1', OBJECT_CONSTRUCT('inner_key1A', 'example1', 'inner_key1B', 'example2'),
           'outer_key2', OBJECT_CONSTRUCT('inner_key2', 5)
         );

INSERT INTO sample_nested_object (id, nested_object)
  SELECT 2,
         OBJECT_CONSTRUCT(
           'outer_key1', OBJECT_CONSTRUCT('inner_key1A', 'example3', 'inner_key1B', 'example4'),
           'outer_key2', OBJECT_CONSTRUCT('inner_key2', 7)
         );

SELECT * FROM sample_nested_object;

+----+--------------------------------+
| ID | NESTED_OBJECT                  |
+----+--------------------------------+
| 1  | {                              |
|    |   "outer_key1": {              |
|    |     "inner_key1A": "example1", |
|    |     "inner_key1B": "example2"  |
|    |   },                           |
|    |   "outer_key2": {              |
|    |     "inner_key2": 5            |
|    |   }                            |
|    | }                              |
| 2  | {                              |
|    |   "outer_key1": {              |
|    |     "inner_key1A": "example3", |
|    |     "inner_key1B": "example4"  |
|    |   },                           |
|    |   "outer_key2": {              |
|    |     "inner_key2": 7            |
|    |   }                            |
|    | }                              |
+----+--------------------------------+

Adicionar novos pares chave-valor aninhados aos valores OBJECT aninhados¶

O exemplo a seguir adiciona novos pares chave-valor aninhados aos valores OBJECT aninhados na tabela. Ele usa uma expressão CASE para especificar o par chave-valor adicionado para cada linha:

UPDATE sample_nested_object
  SET nested_object = OBJECT_INSERT(
    nested_object,
    'outer_key1',
     OBJECT_INSERT(
       nested_object:outer_key1,
       'inner_key1C',
       CASE
         WHEN id = 1 THEN 'added_value_1'
         WHEN id = 2 THEN 'added_value_2'
       END,
       TRUE
      ),
    TRUE);

SELECT * FROM sample_nested_object;

+----+------------------------------------+
| ID | NESTED_OBJECT                      |
|----+------------------------------------|
|  1 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example1",     |
|    |     "inner_key1B": "example2",     |
|    |     "inner_key1C": "added_value_1" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 5                |
|    |   }                                |
|    | }                                  |
|  2 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example3",     |
|    |     "inner_key1B": "example4",     |
|    |     "inner_key1C": "added_value_2" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 7                |
|    |   }                                |
|    | }                                  |
+----+------------------------------------+

Atualizar os pares chave-valor nos valores OBJECT aninhados¶

O exemplo a seguir atualiza os pares chave-valor aninhados nos valores OBJECT na tabela:

UPDATE sample_nested_object
  SET nested_object = OBJECT_INSERT(
    nested_object,
    'outer_key2',
    OBJECT_INSERT(
      nested_object:outer_key2,
      'inner_key2',
      CASE
        WHEN id = 1 THEN 6
        WHEN id = 2 THEN 8
      END,
      TRUE),
    TRUE);

SELECT * FROM sample_nested_object;

+----+------------------------------------+
| ID | NESTED_OBJECT                      |
|----+------------------------------------|
|  1 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example1",     |
|    |     "inner_key1B": "example2",     |
|    |     "inner_key1C": "added_value_1" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 6                |
|    |   }                                |
|    | }                                  |
|  2 | {                                  |
|    |   "outer_key1": {                  |
|    |     "inner_key1A": "example3",     |
|    |     "inner_key1B": "example4",     |
|    |     "inner_key1C": "added_value_2" |
|    |   },                               |
|    |   "outer_key2": {                  |
|    |     "inner_key2": 8                |
|    |   }                                |
|    | }                                  |
+----+------------------------------------+