JavaScript Object Notation SQL rap

#json#json_contains#json_extract#mysql#sql#structured-data#strukturierte-daten

Databases structured data store vaD wonderful. 'ach reasons Daq Dun pagh 'ej complex data structures SQL via store neH. misvam: image metadata (EXIF data) database Daq store, simplicity vaD values:

focal length
exposure time
author information

table fields Hoch value vaD chenmoH laH

CREATE TABLE `fotos` (
  `brennweite` INT,
  `belichtungszeit` INT,
  `urheber` VARCHAR
);

pagh data JSON format Daq table column Daq ghItlh

{"Brennweite": 55, "Belichtungszeit": 120, "Urheber": "devdave"}

JSON format real advantage: object instances JSON Daq/vo' quickly (de)serialize. PHP Daq object

class Foto {
  public $brennweite = 55;
  public $belichtungszeit = 120;
  public $urheber = "devdave";
}
$foto = new Foto();

ghaj vaj

json_encode($foto)

serialize 'ej JSON string instance ghaj, database Daq store laH. Foto class structure choH Hoch database follow tedious 'ej error room. vaj data directly JSON format database Daq store sensible. 'ach data later chay' access?

1. JSON_EXTRACT(%DATEN%, %PFAD%)

JSON_EXTRACT data JSON string vo' extract laH. first parameter JSON string (data provide). jIH case Daq column name (metadata contain). path attribute path specify (output neH). JSON string:

{"Brennweite": 55, "Belichtungszeit": 120, "Urheber": "devdave"}

"Urheber" value access:

SELECT
JSON_EXTRACT('{
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Urheber": "devdave"
}', '$.Urheber');

deeper nested levels access je laH. "Urheber" field "Personen" Dung vaj:

SELECT
JSON_EXTRACT('{
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Personen": ["Urheber": "devdave", "Model": "Hannah"]
}', '$.Personen.Urheber');

value lists index via access. multiple authors vaj first (= "devdave") access:

SELECT JSON_EXTRACT(
  '{"Brennweite": 55, "Belichtungszeit": 120,
  "Personen": ["Urheber": {"devdave", "john wayne"},
  "Model": "Hannah"]}',
  '$.Personen.Urheber[0]');

2. JSON_CONTAINS(%HAYSTACK%, %NEEDLE%, '%PATH%')

JSON_CONTAINS value key Dung chay' law' tu'lu' information return. first JSON string, element search (120 pagh Max), (hypothetical) path element Daq.

JSON_CONTAINS({
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Personen": [
    "Urheber": {"devdave", "john wayne"},
    "Model": "Hannah"
  ]
},
120,
'$.Belichtungszeit');

1 return.

JSON_CONTAINS({
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Personen": [
    "Urheber": {"devdave", "john wayne"},
    "Model": "Hannah"
  ]
},
"Max",
'$.Personen.Urheber[0]');

0 return.

Sources: dev.mysql.com/doc/refman/8.0/en/json-search-functions.html