JSON Duality Views: Acesso flexível a dados relacionais em formato JSON

JSON Duality Views: Uma nova forma de acessar dados relacionais como documentos JSON

Depois de assistir a excelente apresentação do mestre Mario Barduchi no GUOB, resolvi arriscar e escrever sobre essa incrível feature que estará disponível no Oracle 23c.

Caso queira ter acesso ao material utilizado por ele em sua palestra, basta acessar o blog do Mario e se deliciar !

JSON e o Modelo Relacional

JSON (JavaScript Object Notation) é um formato popular para representar e trocar dados na web. JSON é baseado em objetos, que são coleções de pares nome-valor, e arrays, que são listas ordenadas de valores. Um documento JSON é um objeto ou um array que pode conter outros objetos e arrays aninhados.

JSON é flexível, auto-descritivo e independente de linguagem. Isso faz com que JSON seja adequado para aplicações centradas em documentos, que lidam com objetos complexos e hierárquicos que podem mudar frequentemente.

No entanto, JSON também tem algumas limitações. JSON não representa relações entre documentos, o que significa que valores que fazem parte de um documento não podem ser compartilhados por outros. Isso leva à duplicação de dados entre diferentes documentos, o que pode introduzir inconsistências quando os documentos são atualizados.

O modelo relacional, por outro lado, decompõe objetos de aplicação em tabelas normalizadas, que são explicitamente relacionadas mas cujo conteúdo é independente. Isso permite uma combinação flexível e eficiente de dados (junção) que é rigorosamente correta e confiável. Isso evita inconsistências e outros problemas com a duplicação de dados, mas sobrecarrega os desenvolvedores de aplicação com a definição de um mapeamento entre seus objetos de aplicação e as tabelas relacionais. Mudanças na aplicação podem exigir mudanças no esquema das tabelas, o que pode dificultar o desenvolvimento ágil.

JSON Duality Views é uma nova funcionalidade introduzida na versão 23c do banco de dados Oracle, que combina as vantagens de usar documentos JSON com as vantagens do modelo relacional, evitando as limitações de cada um.

O que são JSON Duality Views?

JSON Duality Views são visões de banco de dados que expõem dados armazenados em tabelas relacionais como documentos JSON. Os documentos são materializados – gerados sob demanda, não armazenados como tal. As visões de dualidade dão aos seus dados tanto uma dualidade conceitual quanto operacional: eles são organizados tanto relacionalmente quanto hierarquicamente.

Você pode basear diferentes visões de dualidade em dados armazenados em uma ou mais das mesmas tabelas, fornecendo diferentes hierarquias JSON sobre os mesmos dados compartilhados. Isso significa que as aplicações podem acessar (criar, consultar, modificar) os mesmos dados como um conjunto de documentos JSON ou como um conjunto de tabelas e colunas relacionadas, e ambos os métodos podem ser empregados ao mesmo tempo.

Aplicações centradas em documentos podem usar APIs de documento, como Simple Oracle Document Access (SODA), Oracle Database API for MongoDB e Oracle REST Data Services (ORDS), ou podem usar funções SQL/JSON. Você pode manipular documentos realizados por visões de dualidade da maneira que você está acostumado, usando seus drivers usuais, frameworks, ferramentas e métodos de desenvolvimento. Em particular, as aplicações podem usar qualquer linguagem de programação – os documentos JSON são a língua franca.

Outras aplicações, como análise de dados, relatórios e aprendizado de máquina, podem fazer uso dos mesmos dados diretamente, relacionalmente (como um conjunto de linhas e colunas da tabela), usando linguagens como SQL, PL/SQL, C e JavaScript.

Como criar JSON Duality Views?

Para criar uma visão de dualidade JSON, você precisa usar a instrução SQL CREATE JSON RELATIONAL DUALITY VIEW. Essa instrução requer os seguintes parâmetros:

O nome da visão
A especificação da tabela raiz (nível superior) da visão
A especificação das colunas da tabela raiz que fornecem os valores dos campos do documento JSON
A especificação das tabelas filhas (aninhadas) da visão, se houver
A especificação das colunas das tabelas filhas que fornecem os valores dos campos do documento JSON
A especificação das restrições de chave estrangeira que relacionam as tabelas raiz e filhas

A sintaxe geral da instrução é a seguinte:

CREATE JSON RELATIONAL DUALITY VIEW view_name
  AS SELECT root_table.column_name AS json_field_name, ...
  FROM root_table
  LEFT JOIN child_table ON root_table.foreign_key = child_table.primary_key
  ...

Exemplo prático de JSON Duality Views

Para ilustrar o uso de JSON Duality Views, vamos considerar um exemplo simples de um banco de dados de uma loja online. O banco de dados contém três tabelas: customers, orders e order_items. A tabela customers armazena os dados dos clientes, como id, nome, email e endereço. A tabela orders armazena os dados dos pedidos, como id, data, status e id do cliente. A tabela order_items armazena os dados dos itens de cada pedido, como id, quantidade, preço e id do pedido.

O esquema relacional do banco de dados é mostrado na figura abaixo:

Os scripts SQL para criar as tabelas e inserir alguns dados de exemplo são os seguintes:

-- Criar a tabela customers
CREATE TABLE customers (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50) NOT NULL,
  email VARCHAR2(50) UNIQUE NOT NULL,
  address VARCHAR2(100) NOT NULL
);

-- Inserir alguns dados na tabela customers
INSERT INTO customers VALUES (1, 'Alice', 'alice@example.com', 'Rua A, 123');
INSERT INTO customers VALUES (2, 'Bob', 'bob@example.com', 'Rua B, 456');
INSERT INTO customers VALUES (3, 'Charlie', 'charlie@example.com', 'Rua C, 789');

-- Criar a tabela orders
CREATE TABLE orders (
  id NUMBER PRIMARY KEY,
  date DATE NOT NULL,
  status VARCHAR2(10) NOT NULL CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED')),
  customer_id NUMBER NOT NULL REFERENCES customers(id)
);

-- Inserir alguns dados na tabela orders
INSERT INTO orders VALUES (1, DATE '2023-04-01', 'PENDING', 1);
INSERT INTO orders VALUES (2, DATE '2023-04-02', 'SHIPPED', 2);
INSERT INTO orders VALUES (3, DATE '2023-04-03', 'DELIVERED', 3);

-- Criar a tabela order_items
CREATE TABLE order_items (
  id NUMBER PRIMARY KEY,
  quantity NUMBER NOT NULL CHECK (quantity > 0),
  price NUMBER NOT NULL CHECK (price >= 0),
  order_id NUMBER NOT NULL REFERENCES orders(id)
);

-- Inserir alguns dados na tabela order_items
INSERT INTO order_items VALUES (1, 2, 10.00, 1);
INSERT INTO order_items VALUES (2, 1, 20.00, 1);
INSERT INTO order_items VALUES (3, 3, 15.00, 2);
INSERT INTO order_items VALUES (4, 4, 25.00, 3);
INSERT INTO order_items VALUES (5, 5, 30.00, 3);

Agora vamos criar uma visão de dualidade JSON que expõe os dados das três tabelas como documentos JSON. Cada documento JSON representa um pedido e contém as seguintes informações:

O id do pedido
A data do pedido
O status do pedido
O nome do cliente que fez o pedido
O email do cliente que fez o pedido
O endereço do cliente que fez o pedido
Um array de itens do pedido, cada um contendo:
- O id do item
- A quantidade do item
- O preço do item

A instrução SQL para criar a visão de dualidade JSON é a seguinte:

-- Criar a visão de dualidade JSON orders_dv

CREATE JSON RELATIONAL DUALITY VIEW orders_dv AS 
SELECT o.id AS "orderId",
       o.date AS "orderDate",
       o.status AS "orderStatus",
       c.name AS "customerName",
       c.email AS "customerEmail",
       c.address AS "customerAddress",
       i.id AS "itemId",
       i.quantity AS "itemQuantity",
       i.price AS "itemPrice"
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id LEFT JOIN order_items i ON o.id = i.order_id;

-- A visão de dualidade JSON orders_dv foi criada com sucesso

Agora podemos consultar a visão de dualidade JSON orders_dv usando APIs de documento ou funções SQL/JSON. Por exemplo, podemos usar a função SQL `JSON_QUERY` para retornar os documentos JSON que correspondem a um critério de filtro. O exemplo abaixo retorna os documentos JSON dos pedidos que têm o status “PENDING”:

-- Consultar a visão de dualidade JSON orders_dv usando JSON_QUERY

SELECT JSON_QUERY(orders_dv, '$') AS order_json
FROM orders_dv
WHERE orderStatus = 'PENDING';

-- O resultado é o seguinte:

ORDER_JSON
--------------------------------------------------------------------------------
{"orderId":1,"orderDate":"2023-04-01","orderStatus":"PENDING","customerName":"Alice","customerEmail":"alice@example.com","customerAddress":"Rua A, 123","itemId":1,"itemQuantity":2,"itemPrice":10}
{"orderId":1,"orderDate":"2023-04-01","orderStatus":"PENDING","customerName":"Alice","customerEmail":"alice@example.com","customerAddress":"Rua A, 123","itemId":2,"itemQuantity":1,"itemPrice":20}

Podemos ver que os documentos JSON contêm as informações solicitadas sobre os pedidos e os clientes. Observe que cada item do pedido é representado como um documento separado, mas com os mesmos dados do pedido e do cliente. Isso é porque a visão de dualidade JSON usa uma junção externa à esquerda para combinar as tabelas relacionais, o que significa que cada linha da tabela raiz (orders) é combinada com todas as linhas correspondentes da tabela filha (order_items).

Se quisermos agrupar os itens do pedido em um array dentro de cada documento JSON, podemos usar a função SQL JSON_ARRAYAGG para agregar os valores dos campos dos itens em um array. O exemplo abaixo mostra como fazer isso:

-- Consultar a visão de dualidade JSON orders_dv usando JSON_ARRAYAGG

SELECT JSON_OBJECT(
  'orderId' VALUE orderId,
  'orderDate' VALUE orderDate,
  'orderStatus' VALUE orderStatus,
  'customerName' VALUE customerName,
  'customerEmail' VALUE customerEmail,
  'customerAddress' VALUE customerAddress,
  'orderItems' VALUE JSON_ARRAYAGG(
    JSON_OBJECT(
      'itemId' VALUE itemId,
      'itemQuantity' VALUE itemQuantity,
      'itemPrice' VALUE itemPrice
    )
  )
) AS order_json
FROM orders_dv
GROUP BY orderId, orderDate, orderStatus, customerName, customerEmail, customerAddress;

-- O resultado é o seguinte:

ORDER_JSON
--------------------------------------------------------------------------------
{"orderId":1,"orderDate":"2023-04-01","orderStatus":"PENDING","customerName":"Alice","customerEmail":"alice@example.com","customerAddress":"Rua A, 123","orderItems":[{"itemId":1,"itemQuantity":2,"itemPrice":10},{"itemId":2,"itemQuantity":1,"itemPrice":20}]}
{"orderId":2,"orderDate":"2023-04-02","orderStatus":"SHIPPED","customerName":"Bob","customerEmail":"bob@example.com","customerAddress":"Rua B, 456","orderItems":[{"itemId":3,"itemQuantity":3,"itemPrice":15}]}
{"orderId":3,"orderDate":"2023-04-03","orderStatus":"DELIVERED","customerName":"Charlie","customerEmail":"charlie@example.com","customerAddress":"Rua C, 789","orderItems":[{"itemId":4,"itemQuantity":4,"itemPrice":25},{"itemId":5,"itemQuantity":5,"itemPrice":30}]}

Podemos ver que agora os documentos JSON têm um campo “orderItems” que contém um array de objetos com os dados dos itens do pedido.

CRUD (Create, Read, Update, Delete)

A seguir, vou mostrar como realizar as operações CRUD usando a visão de dualidade JSON orders_dv. 🙌

Create

Para inserir (criar) um novo documento na visão de dualidade JSON orders_dv, podemos usar a instrução SQL INSERT INTO com a função SQL JSON_OBJECT para construir o documento JSON a partir dos valores das colunas da tabela raiz (orders) e da tabela filha (order_items). Por exemplo, vamos inserir um novo pedido com as seguintes informações:

Id: 4
Data: 2023-04-04
Status: PENDING
Id do cliente: 1
Itens:
- Id: 6, quantidade: 2, preço: 40.00
- Id: 7, quantidade: 3, preço: 50.00

A instrução SQL para inserir esse documento na visão de dualidade JSON orders_dv é a seguinte:

-- Inserir um novo documento na visão de dualidade JSON orders_dv
INSERT INTO orders_dv VALUES (
  JSON_OBJECT(
    'orderId' VALUE 4,
    'orderDate' VALUE DATE '2023-04-04',
    'orderStatus' VALUE 'PENDING',
    'customerName' VALUE (SELECT name FROM customers WHERE id = 1),
    'customerEmail' VALUE (SELECT email FROM customers WHERE id = 1),
    'customerAddress' VALUE (SELECT address FROM customers WHERE id = 1),
    'orderItems' VALUE JSON_ARRAY(
      JSON_OBJECT(
        'itemId' VALUE 6,
        'itemQuantity' VALUE 2,
        'itemPrice' VALUE 40.00
      ),
      JSON_OBJECT(
        'itemId' VALUE 7,
        'itemQuantity' VALUE 3,
        'itemPrice' VALUE 50.00
      )
    )
  )
);

-- O documento foi inserido com sucesso na visão de dualidade JSON orders_dv

Podemos verificar se o documento foi inserido corretamente consultando a visão de dualidade JSON orders_dv usando a função SQL JSON_QUERY com um critério de filtro pelo id do pedido:

-- Consultar a visão de dualidade JSON orders_dv usando JSON_QUERY
SELECT JSON_QUERY(orders_dv, '$') AS order_json
FROM orders_dv
WHERE orderId = 4;

-- O resultado é o seguinte:

ORDER_JSON
--------------------------------------------------------------------------------
{"orderId":4,"orderDate":"2023-04-04","orderStatus":"PENDING","customerName":"Alice","customerEmail":"alice@example.com","customerAddress":"Rua A, 123","itemId":6,"itemQuantity":2,"itemPrice":40}
{"orderId":4,"orderDate":"2023-04-04","orderStatus":"PENDING","customerName":"Alice","customerEmail":"alice@example.com","customerAddress":"Rua A, 123","itemId":7,"itemQuantity":3,"itemPrice":50}

Podemos ver que o documento foi inserido na visão de dualidade JSON orders_dv com as informações solicitadas.

Read

Para ler (consultar) os documentos da visão de dualidade JSON orders_dv, podemos usar as APIs de documento ou as funções SQL/JSON. Por exemplo, podemos usar a função SQL JSON_VALUE para retornar os valores de um campo específico dos documentos JSON que correspondem a um critério de filtro. O exemplo abaixo retorna os nomes dos clientes que fizeram pedidos com o status “SHIPPED”:

-- Consultar a visão de dualidade JSON orders_dv usando JSON_VALUE
SELECT DISTINCT JSON_VALUE(orders_dv, '$.customerName') AS customer_name
FROM orders_dv
WHERE orderStatus = 'SHIPPED';

-- O resultado é o seguinte:

CUSTOMER_NAME
--------------------------------------------------------------------------------
Bob

Podemos ver que o único cliente que fez um pedido com o status “SHIPPED” foi o Bob.

Update

Para atualizar (modificar) um documento na visão de dualidade JSON orders_dv, podemos usar a instrução SQL UPDATE com a função SQL JSON_MERGEPATCH para mesclar as alterações desejadas no documento JSON existente. Por exemplo, vamos atualizar o status do pedido com o id 2 para “DELIVERED”:

-- Atualizar um documento na visão de dualidade JSON orders_dv
UPDATE orders_dv SET orders_dv = JSON_MERGEPATCH(orders_dv, '{"orderStatus":"DELIVERED"}')
WHERE orderId = 2;

-- O documento foi atualizado com sucesso na visão de dualidade JSON orders_dv

Podemos verificar se o documento foi atualizado corretamente consultando a visão de dualidade JSON orders_dv usando a função SQL JSON_QUERY com um critério de filtro pelo id do pedido:

-- Consultar a visão de dualidade JSON orders_dv usando JSON_QUERY
SELECT JSON_QUERY(orders_dv, '$') AS order_json
FROM orders_dv
WHERE orderId = 2;

-- O resultado é o seguinte:

ORDER_JSON
--------------------------------------------------------------------------------
{"orderId":2,"orderDate":"2023-04-02","orderStatus":"DELIVERED","customerName":"Bob","customerEmail":"bob@example.com","customerAddress":"Rua B, 456","itemId":3,"itemQuantity":3,"itemPrice":15}

Podemos ver que o status do pedido foi alterado para “DELIVERED”.

Delete

Para deletar (remover) um documento na visão de dualidade JSON orders_dv, podemos usar a instrução SQL DELETE com um critério de filtro pelo id do pedido. Por exemplo, vamos deletar o pedido com o id 3:

-- Deletar um documento na visão de dualidade JSON orders_dv
DELETE FROM orders_dv WHERE orderId = 3;

-- O documento foi deletado com sucesso da visão de dualidade JSON orders_dv

Podemos verificar se o documento foi deletado corretamente consultando a visão de dualidade JSON orders_dv usando a função SQL JSON_QUERY com um critério de filtro pelo id do pedido:

-- Consultar a visão de dualidade JSON orders_dv usando JSON_QUERY
SELECT JSON_QUERY(orders_dv, '$') AS order_json
FROM orders_dv
WHERE orderId = 3;

-- O resultado é vazio, indicando que o documento não existe mais na visão de dualidade JSON orders_dv

ORDER_JSON
--------------------------------------------------------------------------------

Conclusão

JSON Duality Views é uma nova funcionalidade do banco de dados Oracle que permite acessar dados relacionais como documentos JSON. Isso oferece aos desenvolvedores e usuários uma maior flexibilidade e conveniência para trabalhar com dados centrados em documentos e relacionalmente ao mesmo tempo. As visões de dualidade são materializadas sob demanda e não requerem armazenamento adicional ou redundante. As visões de dualidade podem ser criadas usando a instrução SQL CREATE JSON RELATIONAL DUALITY VIEW e podem ser consultadas usando APIs de documento ou funções SQL/JSON.

No próximo artigo, mostrarei como fazer o CRUD através do resultado da execução de uma API.

Abs

Referências