MC536 - Mysql Json: JavaScript Object Notation

Prof. Célio Guimarães     IC - Unicamp

Atualizado em 14 Nov 2022

Referências: ver links nos cabeçalhos dos exemplos.

Orientação a Objetos nos SGBDs modernos

Oracle e Postgres, dentre outros, introduziram recursos de OO nos seus sgbds, eliminando de fato a 1a Forma Normal proposta por Codd (provavelmente por razões de eficiência), que proibe tabelas dentro de tabelas.
No lugar desses recursos de OO, Mysql implementa um recurso extremamente interessante, JSON, que permite estruturar documentos de forma aninhada. JSON substituiu totalmente o padrão XML, pois é uma forma mais compacta de representar documentos (XML é um "padrão" criado para estender HTML, mas que na prática, não "pegou").

JSON se tornou a escolha preferida para representar documentos nos SGBDs NOSQL modernos como MongoDB, Cassandra e outros, permitindo uma maior flexibilidade com campos facultativos e/ou de tamanho variável dentro de documentos. Um documento JSON é comumente dito como "semi-estruturado" ao contrário da rigidez das tabelas relacionais com seu número fixo de colunas. Além disso, JSON (JavaScript Object Notation), tem o recurso chamado key,value ("chave, valor"), que permite fazer buscas eficientes no documento, inclusive com a criação de índices para acelerar as buscas.

Vamos introduzir JSON através de exemplos auto-explicativos, retirados da internet: 

https://www.ibm.com/docs/no/db2/11.5?topic=documents-json-nested-o

    In JSON, values must be one of the following data types:
    a string
    a number
    an object (JSON object)
    an array
    a boolean
    null
Exemplo de um catãlogo de pessoas:
https://en.wikipedia.org/wiki/JSON 
{
  "firstName": "John",
  "lastName": "Smith",
  "isAlive": true,
  "age": 27,
  "address": {
    "streetAddress": "21 2nd Street",
    "city": "New York",
    "state": "NY",
    "postalCode": "10021-3100"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    }
  ],
  "children": [
      "Catherine",
      "Thomas",
      "Trevor"
  ],
  "spouse": null
}
Exemplo de um catálogo de livros:
https://www.ibm.com/docs/no/db2/11.5?topic=documents-json-nested-o 
{"isbn": "123-456-222",  
 "author": 
    {
      "lastname": "Doe",
      "firstname": "Jane"
    },
"editor": 
    {
      "lastname": "Smith",
      "firstname": "Jane"
    },
  "title": "The Ultimate Database Study Guide",  
  "category": ["Non-Fiction", "Technology"]
 }

Consultas em Mysql sobre documentos json

Mysql possui diversas funções que podem ser invocadas dentro de um comando select para extrair dados de um documento json através de uma chave no documento. JSON_EXTRACT é uma delas. Nos exemplos a seguir, por simplicidade, o documento json é o primeiro parâmetro do comando select; o segundo parâmetro é a chave procurada no documento. Na prática ele seria uma "tabela com colunas no formato json": 
JSON Object:
     SELECT JSON_EXTRACT('{"a":1,"b":"stringdata"}','$.b');
output:
     "stringdata"

JSON Object Multi Level:
    SELECT JSON_EXTRACT('{"a":1,"b":"stringdata","c":{"d":33}}','$.c.d') 
output:
    33

JSON Array:
    SELECT JSON_EXTRACT('[1,2,3,4]','$[0]')  
output:
    1
JSON Multilevel Array:
     SELECT JSON_EXTRACT('[1,2,3,[4,5]]','$[3][1]');
output:
     5
Vejamos agora um exemplo mais prático incluindo a criação de uma tabela com uma coluna do tipo JSON e várias consultas (comandos select) sobre a a mesma: 
Ref: https://www.digitalocean.com/community/tutorials/working-with-json-in-mysql

CREATE TABLE 'e_store'.'products'(
	'id' INT UNSIGNED NOT NULL AUTO_INCREMENT ,
	'name' VARCHAR(250) NOT NULL ,
	'brand_id' INT UNSIGNED NOT NULL ,
	'category_id' INT UNSIGNED NOT NULL ,
	'attributes' JSON NOT NULL ,
         PRIMARY KEY('id') 
        )
Nota: (i) e_store é a BD e products a tabela sendo criada nela.
      (ii) neste ponto o tutorial insere vários produtos no json, contendo
           dados detalhados para televisões, celulares e câmeras. A partir daí
           ele passa a fazer consultas sobre e tabela products.

This query will produce 15 OBJECT results to represent all of the products:
five televisions, five mobile phones, and five cameras:

     SELECT JSON_TYPE(attributes) FROM 'e_store'.'products';

When you wish to select rows using a JSON field, you should be familiar 
with the concept of a path expression. Path expressions use a dollar sign 
symbol ($) and the target object keys.
When used in combination with the JSON_EXTRACT function, you can retrieve the values for the specified column.
Now consider a scenario where you are interested in all of the televisions 
that have at least one USB and one HDMI port:
 
     SELECT	*
     FROM 'e_store'.'products'
     WHERE 'category_id' = 1
       AND JSON_EXTRACT('attributes' , '$.ports.usb') > 0
       AND JSON_EXTRACT('attributes' , '$.ports.hdmi') > 0;

Alternatively, the JSON_EXTRACT function has the alias -> that you can use to make your queries more readable:

     SELECT *
     FROM 'e_store'.'products'
     WHERE 'category_id' = 1
     AND 'attributes' -> '$.ports.usb' > 0
     AND 'attributes' -> '$.ports.hdmi' > 0;