
## JSON COLUMNS
### Default
json_object JSON NOT NULL DEFAULT (JSON_OBJECT()) COMMENT ' default needs mysql version >=  8.0.13',
    o
    json_array JSON NOT NULL DEFAULT (JSON_ARRAY()) COMMENT ' default needs mysql version >=  8.0.13',
    * tip update nulls then alter, if needed

### Index (Single level, json_object, {})
ALTER TABLE book ADD COLUMN title VARCHAR(191) GENERATED ALWAYS AS (properties ->> "$.title");
CREATE INDEX book_title_idx ON book (title);
or
    CREATE TALBE jsonIndexed (
        content JSON NOT NULL DEFAULT (JSON_OBJECT()),
        INDEX json_indexing_content_num_idx ((JSON_VALUE(content, '$.num' RETURNING SIGNED))),
        INDEX json_indexing_content_str_idx ((JSON_VALUE(content, '$.str' RETURNING VARCHAR(200)))),
    );


### Where (Single level, json_object, {})
SELECT isbn FROM book WHERE properties ->> "$.title" = 'High-Performance Java Persistence';

### Update a key (Single level, json_object, {})
UPDATE book SET
    properties = JSON_SET( properties, '$.title', 'High-Performance Java Persistence, 2nd edition' )
WHERE isbn = '978-9730228236'

### JSON ARRAY, [], TABLE JOIN EXAMPLE
SELECT /*ejemplo*/
    nb.nota_bodega_id,  a.top_ord, b.bodega, nb.entrada_salida, nb.numero, nb.tipo,
    ob.clave as destino, nb.ayudantes,
    ay.ayudante_id, a.display, ay.es_encargado,  obay.clave as puede_ayudar_en,
    oba.activo as 'ayudante_activo_destino',
    a.alta_db, a.alta_por
FROM nota_bodega nb
         JOIN bodega b on nb.bodega_id = b.bodega_id
         JOIN origen_bodega ob on nb.origen_id = ob.origen_bodega_id
         JOIN JSON_TABLE(
        nb.ayudantes_json, '$[*]'  COLUMNS(
            top_ord FOR ORDINALITY,
            ayudante_id VARCHAR(32) PATH '$.id',
            fue_error CHAR(2) PATH '$.fue_error',
            alta_db DATETIME PATH '$.alta_db',
            alta_por VARCHAR(32) PATH '$.alta_por',
            display VARCHAR(191) PATH '$.display'
            )
    ) as a ON a.fue_error = 'No'
         JOIN ayudantes ay ON ay.ayudante_id = a.ayudante_id
         LEFT OUTER JOIN origen_bodega_ayudantes oba ON
            oba.ayudante_id = ay.ayudante_id AND
            ob.origen_bodega_id = oba.origen_bodega_id
         LEFT OUTER JOIN origen_bodega obay ON oba.origen_bodega_id = obay.origen_bodega_id
WHERE nb.tipo <> 'Cancelacion' AND nb.destino_fuerza_ok = 'No'
  AND (oba.origen_bodega_id IS NULL OR a.ayudante_id = '484d7ee23af386f611ed1fe0112d5cb0');



