Extraire des données depuis un tableau d'objets JSON

**bricoreur** · 06/11/2022, 23h09

Salut,

BD : mysql8
serveur : debian 10
PHP 7.4

question 1 : vaut-il mieux avoir 10 000 lignes qui contiennent un champ Json (3 niveaux) ou 100 000 lignes sans champ json ? quel est le plus rapide ?

Question 2 : pour ma compréhension

J'aimerais savoir quelle requête SQL effectuer pour extraire des données d'un tableau d'objets json

Par exemple, récupérer des données où style = "Hard"

J'ai essayé:
SELECT *
FROM maTable
WHERE disque->>"$.Style" = "Hard"

comment accéder au niveau inférieur ?

Ma table:
id
producteur
disque=json

Voici la structure de mon objet JSON "disque"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
[
    {
        "Artiste": "AC/DC",
        "Année": 2010,
        "Album": [
                "AlbumName": "Puissance",
                "Style": "Hard"
        ]
    }, {
        "Artiste": "U2",
        "Année": 2011,
        "Album": [
                "NomAlbum": "Faux",
                "Style": "Pop"
        ]
    }, {
        "Artiste": "IRON",
        "Année 2014,
        "Album": [
                "NomAlbum": "Nouveau",
                "Style": "Hard"
        ]
    }
]

Merci pour vos retours

**Séb.** · 11/11/2022, 00h52

question 1 : vaut-il mieux avoir 10 000 lignes qui contiennent un champ Json (3 niveaux) ou 100 000 lignes sans champ json ? quel est le plus rapide ?

Il faut tester. JSON peut aussi être très rapide grâce à des index.

Il y a un problème sur ta structure JSON, Album ressemble à un objet mais tu le représentes comme un tableau. Je considère qu'il s'agit d'un objet.
De plus tu as un coup Album.AlbumName et un autre Album.NomAlbum, ça n'aide pas

Et attention, les " ne servent normalement pas délimiter une chaîne SQL, mais un nom de colonne ou table. Il vaut mieux utiliser ' pour délimiter une chaîne SQL.

Comment aller plus en profondeur ? Il suffit d'enchaîner les ".".

Pour explorer un tableau JSON, tu peux utiliser JSON_TABLE(). Avec ta data :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SET @data = '[
	{ "Artiste": "AC/DC", "Année": 2010, "Album": { "AlbumName": "Puissance", "Style": "Hard" }},
	{ "Artiste": "U2", "Année": 2011, "Album": { "NomAlbum": "Faux", "Style": "Pop" }},
	{ "Artiste": "IRON", "Année": 2014, "Album": { "NomAlbum": "Nouveau", "Style": "Hard" }}
]';
 
SELECT ALL
	a.album->>'$.Artiste' AS "Artiste", 
	COALESCE(a.album->>'$.Album.AlbumName', a.album->>'$.Album.NomAlbum') AS "Album", 
	a.album->>'$.Année' AS "Année", 
	a.album AS "JSON"
FROM JSON_TABLE(@data, '$[*]' COLUMNS (album JSON PATH '$')) AS a
WHERE a.album->'$.Album.Style' = 'Hard';

Qui donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Artiste	Album	Année	JSON
AC/DC	Puissance	2010	{"Album": {"Style": "Hard", "AlbumName": "Puissance"}, "Année": 2010, "Artiste": "AC/DC"}
IRON	Nouveau	2014	{"Album": {"Style": "Hard", "NomAlbum": "Nouveau"}, "Année": 2014, "Artiste": "IRON"}

Comme tu auras plusieurs lignes dans ta table, il faudra utiliser un CROSS JOIN.

Si tu veux un exemple concret donne-nous quelques lignes de ta table.

**bricoreur** · 11/11/2022, 14h04

Merci pour votre retour

J'ai essayé de comprendre mais il y a des notions que je ne maîtrise pas notamment FROM JSON_TABLE

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias) // où est-ce que l'on indique la table sur laquelle travailler ? @data ?

Je crois avoir compris JSON_EXTRACT

J'ai également simplifié mon objet.

Je ne sais pas si ma demande est possible, c'est-a-dire extraire quelques données depuis un objet ?

Pour rappel :
mysql 8.0.18

Table : adresse
id
adresseAt : (datetime)
scores : (json)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
 
Table : adresse 
 
enregistrement 1 : champs "scores"
 
[
    {
        "tr": "16",
        "tt": "40",
        "type": "t2a",
        "week": 45,
        "player" : "67"
    },
    {
        "tr": null,
        "tt": null,
        "type": "t2a",
        "week": 45,
        "player" : "71"
    },
    {
        "tr": null,
        "tt": null,
        "type": "t2a",
        "week": 45,
        "player" : "92"
    },
    {
        "tr": null,
        "tt": null,
        "type": "lf",
        "week": 45,
        "player" : "67"
    },
    {
        "tr": 12,
        "tt": 30,
        "type": "lf",
        "week": 45,
        "player" : "71"
    },
    {
        "tr": 20,
        "tt": 40,
        "type": "lf",
        "week": 45,
        "player" : "92"
    },
    {
        "tr": null,
        "tt": null,
        "type": "lf",
        "week": 45,
        "player" : "69"
    }
]
 
Enregistrement 2 : champs "scores"
 
[
    {
        "tr": "40",
        "tt": "50",
        "type": "t2a",
        "week": 46,
        "player" : "67"
    },
    {
        "tr": "35",
        "tt": "50",
        "type": "t2a",
        "week": 46,
        "player" : "71"
    },
    {
        "tr": "20",
        "tt": "40",
        "type": "t2a",
        "week": 46,
        "player" : "92"
    },
    {
        "tr": 9,
        "tt": 10,
        "type": "lf",
        "week": 46,
        "player" : "67"
    },
    {
        "tr": 8,
        "tt": 10,
        "type": "lf",
        "week": 46,
        "player" : "71"
    },
    {
        "tr": 19,
        "tt": 20,
        "type": "lf",
        "week": 46,
        "player" : "92"
    }
]

J'ai essayé cela mais sans succès :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT ALL
a.scores->>"$.tr" AS "TR",
a.scores->>"$.tt" AS "TT",
a.scores->>"$.week" AS "SEMAINE"
FROM JSON_TABLE(@data, "$[*]" COLUMNS (scores JSON PATH "$")) AS a
WHERE a.scores->"$.scores.player" = "67";
 
NB : je n'arrive pas à comprendre la ligne FROM JSON_TABLE : comment lui dire qu'il doit lire la table adresse

Reponse attendue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
TR    TT    SEMAINE
16     40    45
null   null  45
40     50    46
9       10   46

J'espère avoir été assez précis.

**Séb.** · 12/11/2022, 22h55

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias) // où est-ce que l'on indique la table sur laquelle travailler ? @data ?

C'est expr qui est utilisé comme source de données. Cela peut être un littéral JSON, une colonne, une fonction... N'importe quoi évalué en JSON.

Je ne sais pas si ma demande est possible, c'est-a-dire extraire quelques données depuis un objet ?

Oui. Avec JSON_TABLE() tu passes tes champs JSON en colonnes SQL, et ensuite c'est du SQL classique.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT ALL
a.scores->>"$.tr" AS "TR",
a.scores->>"$.tt" AS "TT",
a.scores->>"$.week" AS "SEMAINE"
...

Pour la 2nde fois, il ne FAUT PAS utiliser " pour délimiter des chaînes. Il faut utiliser '
D'autres SGBD que MariaDB / MySQL te retourneraient une erreur.

Avec ton JSON (dont il faudra faire attention au typage des valeurs, ex. "tr" qui contient du numérique parfois représenté en chaîne, d'autres fois non...) :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
with dataset (scores) as (
	values
		row ('[
			{ "tr": "16", "tt": "40", "type": "t2a", "week": 45, "player" : "67" },
			{ "tr": null, "tt": null, "type": "t2a", "week": 45, "player" : "71" },
			{ "tr": null, "tt": null, "type": "t2a", "week": 45, "player" : "92" },
			{ "tr": null, "tt": null, "type": "lf", "week": 45, "player" : "67" },
			{ "tr": 12, "tt": 30, "type": "lf", "week": 45, "player" : "71" },
			{ "tr": 20, "tt": 40, "type": "lf", "week": 45, "player" : "92" },
			{ "tr": null, "tt": null, "type": "lf", "week": 45, "player" : "69" }
		]'),
		row ('[
			{ "tr": "40", "tt": "50", "type": "t2a", "week": 46, "player" : "67" },
			{ "tr": "35", "tt": "50", "type": "t2a", "week": 46, "player" : "71" },
			{ "tr": "20", "tt": "40", "type": "t2a", "week": 46, "player" : "92" },
			{ "tr": 9, "tt": 10, "type": "lf", "week": 46, "player" : "67" },
			{ "tr": 8, "tt": 10, "type": "lf", "week": 46, "player" : "71" },
			{ "tr": 19, "tt": 20, "type": "lf", "week": 46, "player" : "92" }
		]')
)
select all scores.*, dataset.*
from dataset
cross join json_table(scores, '$[*]' columns (
	tr tinyint unsigned path '$.tr',
	tt tinyint unsigned path '$.tt',
	type tinytext path '$.type',
	week tinyint unsigned path '$.week',
	player tinyint unsigned path '$.player'
)) as scores
where scores.player = 67;

Donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
tr	tt	type	week	player	scores
16	40	t2a	45	67	[{ "tr": "16", "tt": "40", "type": "t2a", "week": 45, "player" : "67" }, ...
null	null	lf	45	67	[{ "tr": "16", "tt": "40", "type": "t2a", "week": 45, "player" : "67" }, ...
40	50	t2a	46	67	[{ "tr": "40", "tt": "50", "type": "t2a", "week": 46, "player" : "67" }, ...
9	10	lf	46	67	[{ "tr": "40", "tt": "50", "type": "t2a", "week": 46, "player" : "67" }, ...

Attention, il est généralement insuffisant de coder un numéro de semaine sur 2 chiffres. Que se passe-t-il en fin d'année ? Généralement on code YYYYMM.

**bricoreur** · 13/11/2022, 00h30

C'est noté pour les doubles quotes dans la requête :
Idem pour le numéro de la semaine (merci pour les conseils)

Voilà où j'en suis :

Voici 2 enregistrements :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
 
[
    {
        "tr": "2",
        "tt": "20",
        "type": "t2a",
        "week": 45,
        "poliste": "67"
    },
    {
        "tr": 8,
        "tt": 10,
        "type": "t2a",
        "week": 45,
        "poliste": "71"
    }
]
 
[
    {
        "tr": null,
        "tt": null,
        "type": "t2a",
        "week": 45,
        "poliste": "67"
    },
    {
        "tr": "6",
        "tt": "20",
        "type": "t2a",
        "week": 45,
        "poliste": "71"
    }
]

J'avance un peu dans ma compréhension

Je ne savais pas qu'il y avait un typage. En reprenant votre code : tinyint unsigned j'ai un message d'alerte non bloquant

Warning: #3156 Invalid JSON value for CAST to INTEGER from column TR at row 1

J'ai transformé comme ceci et je n'ai plus d'erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT result.TR, result.TT, result.SEMAINE, result.TYPEADRESSE
FROM adresse 
    CROSS JOIN JSON_TABLE(scores, '$[*]' COLUMNS (TR VARCHAR(7) PATH '$.tr', TT VARCHAR(7) PATH '$.tt', SEMAINE INTEGER PATH '$.week', TYPEADRESSE VARCHAR(5) PATH '$.type', PLAYERID VARCHAR(10) PATH '$.poliste' )) AS result
WHERE result.PLAYERID = '67'

J'obtiens tous les bons résultats.

Cependant, je suis un peu dégouté. Je viens de m'apercevoir qu'en local je travaillais sur :

Type de serveur : MySQL
Version du serveur : 8.0.18 - MySQL Community Server - GPL
Version du protocole : 10

Tout fonctionne!

En revanche, avec mon serveur test en ligne, j'ai la configuration suivante :

Type de serveur : MariaDB
Version du serveur : 10.4.22-MariaDB-1:10.4.22+maria~buster-log - mariadb.org binary distribution
Version du protocole : 10

Et ce qui marche en local ne marche pas en ligne !!!

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(scores, '$[*]' COLUMNS (TR VARCHAR(7) PATH '$.tr', TT VARCHAR(7) PATH '$.tt'...' at line 3

avez-vous une solution (code) ?

j'ai lu que l'on pouvait à partir de 10.5 mariadb installer un plugin mais j'aimerai trouver pour le moment une solution en codage si elle existe.

**Séb.** · 13/11/2022, 11h01

En reprenant votre code : tinyint unsigned j'ai un message d'alerte non bloquant
Warning: #3156 Invalid JSON value for CAST to INTEGER from column TR at row 1

De mon côté ràs sur MySQL 8.0.31 en @@sql_mode = 'ANSI,TRADITIONAL'

Type de serveur : MariaDB
Version du serveur : 10.4.22-MariaDB-1:10.4.22+maria~buster-log - mariadb.org binary distribution
Et ce qui marche en local ne marche pas en ligne !!!

JSON_TABLE() arrive sur MariaDB 10.6, sortie il y a 1 an et demi.
https://mariadb.com/kb/en/json_table/

La requête sur MariaDB 10.6 => https://dbfiddle.uk/ktRdpvmh

**bricoreur** · 13/11/2022, 11h17

J'ai fait un test sur un vitualhost où j'ai upgradé mariadb 10.4 vers 10.6
ça fonctionne
Je sais ce qui me reste à faire ....

Je vous tiendrai informé de ma réussite ou non

La requête sur MariaDB 10.6 => https://dbfiddle.uk/ktRdpvmh (merci pour le lien très utile !!!)

bien à vous

Extraire des données depuis un tableau d'objets JSON

Requêtes MySQL

Discussions similaires

Partager

Partager