Suppression des clés JSON avec une certaine chaîne de caractères en PL/SQL

**LandGreen** · 05/12/2023, 17h49

Actuellement engagé dans un projet PL/SQL, je manipule une variable CLOB (vLIGN) au format JSON, où les clés et les valeurs sont concaténées avec "$FUSI$".
La présence de "$FUSI$" indique que la valeur associée doit être fusionnée avec celle précédente, ce qui équivaut à concaténer les deux valeurs et à supprimer la clé de la dernière occurrence.

Voici un exemple de ma variable 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
 
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 1680000","$FUSI$SALA__PREN":"$FUSI$HADDOCK","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ CAPITAINE"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 2030000","$FUSI$SALA__PREN":"$FUSI$JEANETTE","NDF__DATE_CREA":"10-02-2022","$FUSI$SALA__NOM":"$FUSI$ DUJARDIN"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 4500000","$FUSI$SALA__PREN":"$FUSI$DECKER","NDF__DATE_CREA":"11-04-2022","$FUSI$SALA__NOM":"$FUSI$ BLACKET"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 200000","$FUSI$SALA__PREN":"$FUSI$DECKER","NDF__DATE_CREA":"11-02-2022","$FUSI$SALA__NOM":"$FUSI$ BLACKET"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 2030000","$FUSI$SALA__PREN":"$FUSI$JAIME","NDF__DATE_CREA":"10-02-2022","$FUSI$SALA__NOM":"$FUSI$ LESGATEAUX"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 200000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"11-02-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 150000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"11-02-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 150000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"11-02-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 7150000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"12-01-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 47815","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 8500000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"11-03-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 194","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"26-08-2021","$FUSI$SALA__NOM":"$FUSI$ IRONE"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 6000000","$FUSI$SALA__PREN":"$FUSI$PARFAIT","NDF__DATE_CREA":"11-01-2022","$FUSI$SALA__NOM":"$FUSI$ MONSIEUR"}
vLIGN={"$FUSI$FRAI__TTC":"$FUSI$ 1773","$FUSI$SALA__PREN":"$FUSI$PARFAIT","NDF__DATE_CREA":"09-03-2022","$FUSI$SALA__NOM":"$FUSI$ MONSIEUR"}

si on prend la première ligne :
{"$FUSI$FRAI__TTC":"$FUSI$ 1680000","$FUSI$SALA__PREN":"$FUSI$HADDOCK","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ CAPITAINE"}

Elle devrait être transformée en :

{"FRAI__TTC":"1680000 HADDOCK","NDF__DATE_CREA":"08-04-2022 CAPITAINE"}

nb : si $FUSI$ est situé au début de la chaîne, il doit être ignoré

Comment puis-je effectuer cette suppression en utilisant PL/SQL ? J'ai essayé d'utiliser JSON_TRANSFORM avec l'option ON NULL, mais je ne suis pas sûr de la meilleure approche.

Toute suggestion ou exemple de code serait grandement apprécié. Merci d'avance pour votre aide !

**vanagreg** · 06/12/2023, 09h45

Bonjour,

Le plus simple je pense est de parser le json, supprimer $FUSI$ et concaténer les colonnes, et ensuite regénérer en json. Quelque chose du genre:

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
with v(text) as (
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 1680000","$FUSI$SALA__PREN":"$FUSI$HADDOCK","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ CAPITAINE"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 2030000","$FUSI$SALA__PREN":"$FUSI$JEANETTE","NDF__DATE_CREA":"10-02-2022","$FUSI$SALA__NOM":"$FUSI$ DUJARDIN"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 4500000","$FUSI$SALA__PREN":"$FUSI$DECKER","NDF__DATE_CREA":"11-04-2022","$FUSI$SALA__NOM":"$FUSI$ BLACKET"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 200000","$FUSI$SALA__PREN":"$FUSI$DECKER","NDF__DATE_CREA":"11-02-2022","$FUSI$SALA__NOM":"$FUSI$ BLACKET"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 2030000","$FUSI$SALA__PREN":"$FUSI$JAIME","NDF__DATE_CREA":"10-02-2022","$FUSI$SALA__NOM":"$FUSI$ LESGATEAUX"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 200000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"11-02-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 150000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"11-02-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 150000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"11-02-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 7150000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"12-01-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 47815","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 8500000","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"11-03-2022","$FUSI$SALA__NOM":"$FUSI$ IRONE"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 194","$FUSI$SALA__PREN":"$FUSI$MAN","NDF__DATE_CREA":"26-08-2021","$FUSI$SALA__NOM":"$FUSI$ IRONE"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 6000000","$FUSI$SALA__PREN":"$FUSI$PARFAIT","NDF__DATE_CREA":"11-01-2022","$FUSI$SALA__NOM":"$FUSI$ MONSIEUR"}' from dual union all
                select '{"$FUSI$FRAI__TTC":"$FUSI$ 1773","$FUSI$SALA__PREN":"$FUSI$PARFAIT","NDF__DATE_CREA":"09-03-2022","$FUSI$SALA__NOM":"$FUSI$ MONSIEUR"}' from dual
               ) 
select json_object(
                   'FRAI__TTC'      value replace(jt.FRAI__TTC, '$FUSI$') || replace(jt.SALA__PREN, '$FUSI$', ' '),
                   'NDF__DATE_CREA' value jt.NDF__DATE_CREA || replace(jt.SALA__NOM, '$FUSI$')
                  ) text
from v, json_table(text, '$'
                        columns(
                                FRAI__TTC      varchar2(500) path '$."$FUSI$FRAI__TTC"',
                                SALA__PREN     varchar2(500) path '$."$FUSI$SALA__PREN"',
                                NDF__DATE_CREA varchar2(20)  path '$."NDF__DATE_CREA"',
                                SALA__NOM      varchar2(500) path '$."$FUSI$SALA__NOM"'
                               )
                       ) jt;
 
TEXT                                                                            
--------------------------------------------------------------------------------
{"FRAI__TTC":" 1680000 HADDOCK","NDF__DATE_CREA":"08-04-2022 CAPITAINE"}        
{"FRAI__TTC":" 2030000 JEANETTE","NDF__DATE_CREA":"10-02-2022 DUJARDIN"}        
{"FRAI__TTC":" 4500000 DECKER","NDF__DATE_CREA":"11-04-2022 BLACKET"}           
{"FRAI__TTC":" 200000 DECKER","NDF__DATE_CREA":"11-02-2022 BLACKET"}            
{"FRAI__TTC":" 2030000 JAIME","NDF__DATE_CREA":"10-02-2022 LESGATEAUX"}         
{"FRAI__TTC":" 200000 MAN","NDF__DATE_CREA":"11-02-2022 IRONE"}                 
{"FRAI__TTC":" 150000 MAN","NDF__DATE_CREA":"11-02-2022 IRONE"}                 
{"FRAI__TTC":" 150000 MAN","NDF__DATE_CREA":"11-02-2022 IRONE"}                 
{"FRAI__TTC":" 7150000 MAN","NDF__DATE_CREA":"12-01-2022 IRONE"}                
{"FRAI__TTC":" 47815 MAN","NDF__DATE_CREA":"08-04-2022 IRONE"}                  
{"FRAI__TTC":" 8500000 MAN","NDF__DATE_CREA":"11-03-2022 IRONE"}                
{"FRAI__TTC":" 194 MAN","NDF__DATE_CREA":"26-08-2021 IRONE"}                    
{"FRAI__TTC":" 6000000 PARFAIT","NDF__DATE_CREA":"11-01-2022 MONSIEUR"}         
{"FRAI__TTC":" 1773 PARFAIT","NDF__DATE_CREA":"09-03-2022 MONSIEUR"}            
 
14 rows selected.

**LandGreen** · 06/12/2023, 09h51

merci pour votre réponse
cependant vLIGN est variable et c'est pas en dure, elle peut contenir un nombre non défini de clé-valeur
c'est à dire on peut avoir plusieurs cas :

FRAI__TTC , SALA__PREN , SALA__NOM

ou

FRAI__TTC , SALA__PREN , SALA__NOM, TVA__SALA
FRAI__TTC , SALA__PREN , SALA__NOM, DATE__NAISS_SALA, DATE__EMBAUCH
FRAI__TTC , SALA__PREN , SALA__NOM, FRAIS__HT, SALA__ADV
....

**vanagreg** · 06/12/2023, 09h54

Ah en effet c'est plus compliqué dans ce cas. Tu as quelques exemples avec d'autres chaines?

**LandGreen** · 06/12/2023, 10h19

voici un exemple : ici on peut avoir plusieurs fois FRAI__TTC par exemple, mais il y a d'autres exemples ou j'ai d’autres Clé carrément inexistant ici

Code json :

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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","NDF__DATE_CREA":"08/04/2022 11:51:00","FRAI__TTC":168}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"09/03/2022 14:36:00","FRAI__TTC":177.3}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"11/01/2022 17:19:00","FRAI__TTC":600}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"26/08/2021 14:11:00","FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/03/2022 09:41:00","FRAI__TTC":850}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"08/04/2022 09:40:00","FRAI__TTC":478.15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:33:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:36:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20}
{"SALA__NOM":"LESGATEAUX","SALA__PRENOM":"JAIME","NDF__DATE_CREA":"10/02/2022 19:10:00","FRAI__TTC":203}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/02/2022 16:52:00","FRAI__TTC":20}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/04/2022 17:40:00","FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","FRAI__TTC":203}
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","NDF__DATE_CREA":"08/04/2022 11:51:00","FRAI__TTC":168}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"09/03/2022 14:36:00","FRAI__TTC":177.3}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"11/01/2022 17:19:00","FRAI__TTC":600}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"26/08/2021 14:11:00","FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/03/2022 09:41:00","FRAI__TTC":850}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"08/04/2022 09:40:00","FRAI__TTC":478.15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:33:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:36:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20}
{"SALA__NOM":"LESGATEAUX","SALA__PRENOM":"JAIME","NDF__DATE_CREA":"10/02/2022 19:10:00","FRAI__TTC":203}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/02/2022 16:52:00","FRAI__TTC":20}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/04/2022 17:40:00","FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","FRAI__TTC":203}
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","FRAI__DATE_DEPL":"04/04/2022 00:00:00","FRAI__TTC":126}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"12/01/2022 00:00:00","FRAI__TTC":20}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","FRAI__DATE_DEPL":"09/03/2022 00:00:00","FRAI__TTC":4.7}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","FRAI__DATE_DEPL":"07/03/2022 00:00:00","FRAI__TTC":100}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","FRAI__DATE_DEPL":"26/08/2021 00:00:00","FRAI__TTC":38.8}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","FRAI__DATE_DEPL":"21/09/2016 00:00:00","FRAI__TTC":33.8}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","FRAI__DATE_DEPL":"10/01/2022 00:00:00","FRAI__TTC":600}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"26/08/2021 00:00:00","FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"24/02/2022 00:00:00","FRAI__TTC":220}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"21/02/2022 00:00:00","FRAI__TTC":20}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"23/02/2022 00:00:00","FRAI__TTC":500}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"22/02/2022 00:00:00","FRAI__TTC":110}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"06/04/2022 00:00:00","FRAI__TTC":164}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"05/04/2022 00:00:00","FRAI__TTC":72.15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"04/04/2022 00:00:00","FRAI__TTC":242}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"09/03/2022 00:00:00","FRAI__TTC":400}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"07/03/2022 00:00:00","FRAI__TTC":215}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"10/03/2022 00:00:00","FRAI__TTC":80}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","FRAI__DATE_DEPL":"17/01/2022 00:00:00","FRAI__TTC":50}
{"SALA__NOM":"LESGATEAUX","SALA__PRENOM":"JAIME","FRAI__DATE_DEPL":"02/02/2022 00:00:00","FRAI__TTC":203}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","FRAI__DATE_DEPL":"17/01/2022 00:00:00","FRAI__TTC":20}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","FRAI__DATE_DEPL":"07/04/2022 00:00:00","FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","FRAI__DATE_DEPL":"01/02/2022 00:00:00","FRAI__TTC":203}
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","FRAI__DATE_DEPL":"05/04/2022 00:00:00","FRAI__TTC":42}
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","NDF__DATE_CREA":"08/04/2022 11:51:00","FRAI__TTC":168}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"09/03/2022 14:36:00","FRAI__TTC":177.3}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"11/01/2022 17:19:00","FRAI__TTC":600}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"26/08/2021 14:11:00","FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/03/2022 09:41:00","FRAI__TTC":850}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"08/04/2022 09:40:00","FRAI__TTC":478.15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:33:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:36:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20}
{"SALA__NOM":"LESGATEAUX","SALA__PRENOM":"JAIME","NDF__DATE_CREA":"10/02/2022 19:10:00","FRAI__TTC":203}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/02/2022 16:52:00","FRAI__TTC":20}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/04/2022 17:40:00","FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","FRAI__TTC":203}
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","NDF__DATE_CREA":"08/04/2022 11:51:00","FRAI__TTC":168}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"09/03/2022 14:36:00","FRAI__TTC":177.3}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"11/01/2022 17:19:00","FRAI__TTC":600}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"26/08/2021 14:11:00","FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/03/2022 09:41:00","FRAI__TTC":850}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"08/04/2022 09:40:00","FRAI__TTC":478.15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:33:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:36:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20}
{"SALA__NOM":"LESGATEAUX","SALA__PRENOM":"JAIME","NDF__DATE_CREA":"10/02/2022 19:10:00","FRAI__TTC":203}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/02/2022 16:52:00","FRAI__TTC":20}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/04/2022 17:40:00","FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","FRAI__TTC":203}
{"SALA__NOM":"CAPITAINE","NDF__DATE_CREA":"08/04/2022 11:51:00","FRAI__TTC":168}
{"SALA__NOM":"MONSIEUR","NDF__DATE_CREA":"09/03/2022 14:36:00","FRAI__TTC":177.3}
{"SALA__NOM":"MONSIEUR","NDF__DATE_CREA":"11/01/2022 17:19:00","FRAI__TTC":600}
{"SALA__NOM":"IRONE","NDF__DATE_CREA":"26/08/2021 14:11:00","FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","NDF__DATE_CREA":"11/03/2022 09:41:00","FRAI__TTC":850}
{"SALA__NOM":"IRONE","NDF__DATE_CREA":"08/04/2022 09:40:00","FRAI__TTC":478.15}
{"SALA__NOM":"IRONE","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715}
{"SALA__NOM":"IRONE","NDF__DATE_CREA":"11/02/2022 16:33:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","NDF__DATE_CREA":"11/02/2022 16:36:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20}
{"SALA__NOM":"LESGATEAUX","NDF__DATE_CREA":"10/02/2022 19:10:00","FRAI__TTC":203}
{"SALA__NOM":"BLACKET","NDF__DATE_CREA":"11/02/2022 16:52:00","FRAI__TTC":20}
{"SALA__NOM":"BLACKET","NDF__DATE_CREA":"11/04/2022 17:40:00","FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","NDF__DATE_CREA":"10/02/2022 19:08:00","FRAI__TTC":203}
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","NDF__DATE_CREA":"08/04/2022 11:51:00","FRAI__TTC":168}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"09/03/2022 14:36:00","FRAI__TTC":177.3}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"11/01/2022 17:19:00","FRAI__TTC":600}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"26/08/2021 14:11:00","FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/03/2022 09:41:00","FRAI__TTC":850}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"08/04/2022 09:40:00","FRAI__TTC":478.15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:33:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:36:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20}
{"SALA__NOM":"LESGATEAUX","SALA__PRENOM":"JAIME","NDF__DATE_CREA":"10/02/2022 19:10:00","FRAI__TTC":203}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/02/2022 16:52:00","FRAI__TTC":20}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/04/2022 17:40:00","FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","FRAI__TTC":203}
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","NDF__DATE_CREA":"08/04/2022 11:51:00","FRAI__TTC":168}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"09/03/2022 14:36:00","FRAI__TTC":177.3}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"11/01/2022 17:19:00","FRAI__TTC":600}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"26/08/2021 14:11:00","FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/03/2022 09:41:00","FRAI__TTC":850}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"08/04/2022 09:40:00","FRAI__TTC":478.15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:33:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:36:00","FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20}
{"SALA__NOM":"LESGATEAUX","SALA__PRENOM":"JAIME","NDF__DATE_CREA":"10/02/2022 19:10:00","FRAI__TTC":203}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/02/2022 16:52:00","FRAI__TTC":20}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/04/2022 17:40:00","FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","FRAI__TTC":203}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:33:00","FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:36:00","FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20}
{"SALA__NOM":"LESGATEAUX","SALA__PRENOM":"JAIME","NDF__DATE_CREA":"10/02/2022 19:10:00","FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/02/2022 16:52:00","FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/04/2022 17:40:00","FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203}
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","NDF__DATE_CREA":"08/04/2022 11:51:00","FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"09/03/2022 14:36:00","FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"11/01/2022 17:19:00","FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"26/08/2021 14:11:00","FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/03/2022 09:41:00","FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"08/04/2022 09:40:00","FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15}
{"SALA__NOM":"CAPITAINE","SALA__PRENOM":"HADDOCK","NDF__DATE_CREA":"08/04/2022 11:51:00","FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168,"FRAI__TTC":168}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"09/03/2022 14:36:00","FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3,"FRAI__TTC":177.3}
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","NDF__DATE_CREA":"11/01/2022 17:19:00","FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600,"FRAI__TTC":600}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"26/08/2021 14:11:00","FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4,"FRAI__TTC":19.4}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/03/2022 09:41:00","FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850,"FRAI__TTC":850}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"08/04/2022 09:40:00","FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15,"FRAI__TTC":478.15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:33:00","FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:36:00","FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15,"FRAI__TTC":15}
{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20}
{"SALA__NOM":"LESGATEAUX","SALA__PRENOM":"JAIME","NDF__DATE_CREA":"10/02/2022 19:10:00","FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/02/2022 16:52:00","FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20}
{"SALA__NOM":"BLACKET","SALA__PRENOM":"DECKER","NDF__DATE_CREA":"11/04/2022 17:40:00","FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450,"FRAI__TTC":450}
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203,"FRAI__TTC":203}

voici le total des clé :

SALA__NOM
SALA__PRENOM
SALA__MATRICULE
SALA__CODE_ANAL_1
SALA__POUR_ANAL_1
SALA__CODE_ANAL_2
SALA__POUR_ANAL_2
SALA__CODE_ANAL_3
SALA__POUR_ANAL_3
SALA__CODE_ANAL_4
SALA__POUR_ANAL_4
SALA__CODE_ANAL_5
SALA__POUR_ANAL_5
SALA__CODE_ANAL_6
SALA__POUR_ANAL_6
SALA__CODE_ANAL_7
SALA__POUR_ANAL_7
SALA__CODE_ANAL_8
SALA__POUR_ANAL_8
SALA__CODE_ANAL_9
SALA__POUR_ANAL_9
SALA__CODE_ANAL_10
SALA__POUR_ANAL_10
NDF__REFERENCE
NDF__DATE_CREA
NDF__PERI_CREA
NDF__DATE_BASC
NDF__PERI_BASC
NDF__CODE_NOTE
NDF__LIBE_NOTE
FRAI__DATE_DEPL
FRAI__CODE_FRAI
FRAI__LIBE_FRAI
FRAI__TTC
FRAI__HT
FRAI__TOTA_TVA
FRAI__TVA_20
FRAI__TVA_10
FRAI__TVA_55
FRAI__TVA_21
FRAI__TVA_GENE
FRAI__HT_DEDU
FRAI__TOTA_TVA_DEDU
FRAI__TVA_DEDU_20
FRAI__TVA_DEDU_10
FRAI__TVA_DEDU_55
FRAI__TVA_DEDU_21
FRAI__TVA_DEDU_GENE
FRAI__COMP_CHAR
FRAI__COMP_TVA_20

**LandGreen** · 06/12/2023, 11h15

Envoyé par LandGreen

voici un exemple : ici on peut avoir plusieurs fois FRAI__TTC par exemple, mais il y a d'autres exemples ou j'ai d’autres Clé carrément inexistant ici

voici le total des clé :

SALA__NOM
SALA__PRENOM
SALA__MATRICULE
SALA__CODE_ANAL_1
SALA__POUR_ANAL_1
SALA__CODE_ANAL_2
SALA__POUR_ANAL_2
SALA__CODE_ANAL_3
SALA__POUR_ANAL_3
SALA__CODE_ANAL_4
SALA__POUR_ANAL_4
SALA__CODE_ANAL_5
SALA__POUR_ANAL_5
SALA__CODE_ANAL_6
SALA__POUR_ANAL_6
SALA__CODE_ANAL_7
SALA__POUR_ANAL_7
SALA__CODE_ANAL_8
SALA__POUR_ANAL_8
SALA__CODE_ANAL_9
SALA__POUR_ANAL_9
SALA__CODE_ANAL_10
SALA__POUR_ANAL_10
NDF__REFERENCE
NDF__DATE_CREA
NDF__PERI_CREA
NDF__DATE_BASC
NDF__PERI_BASC
NDF__CODE_NOTE
NDF__LIBE_NOTE
FRAI__DATE_DEPL
FRAI__CODE_FRAI
FRAI__LIBE_FRAI
FRAI__TTC
FRAI__HT
FRAI__TOTA_TVA
FRAI__TVA_20
FRAI__TVA_10
FRAI__TVA_55
FRAI__TVA_21
FRAI__TVA_GENE
FRAI__HT_DEDU
FRAI__TOTA_TVA_DEDU
FRAI__TVA_DEDU_20
FRAI__TVA_DEDU_10
FRAI__TVA_DEDU_55
FRAI__TVA_DEDU_21
FRAI__TVA_DEDU_GENE
FRAI__COMP_CHAR
FRAI__COMP_TVA_20

le total des clés est stocké deans une colonne d'une autre table nommée TABLES_DES_CLES

**vanagreg** · 06/12/2023, 11h54

ok, c'est quoi la règle quand tu as plusieurs fois la même clé? Par exemple:

Code :

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

{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"12/01/2022 16:06:00","FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715,"FRAI__TTC":715}

"FRAI__TTC" apparait plein de fois avec la même valeur. Il faut tout fusionner? D'ailleurs il n'y a plus $FUSI$ dans ces chaines?

**LandGreen** · 06/12/2023, 11h58

Tu peux ignorer cet exemple la ou il y a plusieurs FRAI__TTC
c'est pour dire qu'on peut avoir plein de clé et d'une manière aléatoire

le total des clés est stocké dans une colonne d'une autre table nommée TABLES_DES_CLES

**LandGreen** · 06/12/2023, 13h38

j'ai utilisé ce code pour récupérer les couples clé/valeur

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
     vJsonObj := JSON_OBJECT_T(vLIGN);
 
 
    FOR i IN 1..vJsonObj.GET_SIZE() LOOP
 
        vKey := vJsonObj.GET_KEYS;
        vValue := vJsonObj.GET_STRING(vKey(i));
 
        DBMS_OUTPUT.PUT_LINE('Key: ' ||vKey(i) || '  ****  vValue: ' ||vValue );
    END LOOP;

j'ai ce résultat :

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
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 1680000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$HADDOCK
Key: NDF__DATE_CREA  ****  vValue: 08-04-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ CAPITAINE
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 2030000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$JEANETTE
Key: NDF__DATE_CREA  ****  vValue: 10-02-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ DUJARDIN
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 4500000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$DECKER
Key: NDF__DATE_CREA  ****  vValue: 11-04-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ BLACKET
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 200000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$DECKER
Key: NDF__DATE_CREA  ****  vValue: 11-02-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ BLACKET
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 2030000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$JAIME
Key: NDF__DATE_CREA  ****  vValue: 10-02-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ LESGATEAUX
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 200000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$MAN
Key: NDF__DATE_CREA  ****  vValue: 11-02-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ IRONE
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 150000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$MAN
Key: NDF__DATE_CREA  ****  vValue: 11-02-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ IRONE
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 150000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$MAN
Key: NDF__DATE_CREA  ****  vValue: 11-02-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ IRONE
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 7150000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$MAN
Key: NDF__DATE_CREA  ****  vValue: 12-01-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ IRONE
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 47815
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$MAN
Key: NDF__DATE_CREA  ****  vValue: 08-04-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ IRONE
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 8500000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$MAN
Key: NDF__DATE_CREA  ****  vValue: 11-03-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ IRONE
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 194
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$MAN
Key: NDF__DATE_CREA  ****  vValue: 26-08-2021
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ IRONE
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 6000000
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$PARFAIT
Key: NDF__DATE_CREA  ****  vValue: 11-01-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ MONSIEUR
Key: $FUSI$FRAI__TTC  ****  vValue: $FUSI$ 1773
Key: $FUSI$SALA__PREN  ****  vValue: $FUSI$PARFAIT
Key: NDF__DATE_CREA  ****  vValue: 09-03-2022
Key: $FUSI$SALA__NOM  ****  vValue: $FUSI$ MONSIEUR

maintenant je chercher à traiter le résultat selon ce qui a été mentionné en haut !

des suggestions svp ?

**vanagreg** · 06/12/2023, 14h57

Oui vu que tu as des chaines de longueur différente le mieux en effet c'est de boucler sur les clés comme tu le fais dans cet exemple.
Ensuite vu que tu as l'incrément i tu peux récupérer la valeur précédente vKey(i-1) et vJsonObj.GET_STRING(vKey(i-1).

**LandGreen** · 06/12/2023, 15h05

je n'arrive toujours pas à trouver la solution, c'est compliqué

**skuatamad** · 06/12/2023, 17h16

A améliorer, mais c'est un début :

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
create or replace type t_keyval as object (
		NAME  VARCHAR2(32),
		VAL VARCHAR2(500)
	);
create or replace type tab_t_keyval is table of t_keyval index by binary_integer;
 
 
create or replace function GET_KEY_VALUES(P_JSON_DOC VARCHAR2)
return tab_t_keyval PIPELINED as	
	JO JSON_OBJECT_T := JSON_OBJECT_T(P_JSON_DOC);
    JO_KEYS JSON_KEY_LIST := JO.get_keys();
	t_rec tab_t_keyval := tab_t_keyval();
	j number := 1;
begin
--
    for i in 1..JO_KEYS.count loop
		if i = 1 then 
			t_rec.extend;
			t_rec(t_rec.last) := t_keyval(replace(JO_KEYS(i),'$FUSI$',''), replace(JO.get_string(JO_KEYS(i)),'$FUSI$',''));
		elsif JO_KEYS(i) like '$FUSI$%' then
			t_rec(j).val  := t_rec(j).val || ' ' ||replace(JO.get_string(JO_KEYS(i)),'$FUSI$','');
		else			
			j := j + 1;
			t_rec.extend;
			t_rec(t_rec.last) := t_keyval(JO_KEYS(i),JO.get_string(JO_KEYS(i)));
		end if;
	end loop;
--
	for i in 1..j loop
		--dbms_output.put_line (t_rec(i).name ||' , '||t_rec(i).val);
        pipe row (t_keyval(t_rec(i).name,t_rec(i).val));
	end loop;
end;
/
 
with t as (
select 1 as id, name, val from TABLE(GET_KEY_VALUES('{"$FUSI$FRAI__TTC":"$FUSI$ 1680000","$FUSI$SALA__PREN":"$FUSI$HADDOCK","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ CAPITAINE"}'))
union all
select 2 as id, name, val from TABLE(GET_KEY_VALUES('{"$FUSI$FRAI__TTC":"$FUSI$ 2030000","$FUSI$SALA__PREN":"$FUSI$JEANETTE","NDF__DATE_CREA":"10-02-2022","$FUSI$SALA__NOM":"$FUSI$ DUJARDIN"}'))
    )
select id, json_objectagg (name,val)
  from t
 group by id;

**LandGreen** · 06/12/2023, 18h14

merci pour ton retour
cependant, j'ai eu cette erreur en créant

Code :

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

create or replace type tab_t_keyval is table of t_keyval index by binary_integer;

Elément Type TAB_T_KEYVAL compilé

LINE/COL  ERROR
--------- -------------------------------------------------------------
0/0       PL/SQL: Compilation unit analysis terminated
1/22      PLS-00355: utilisation de la table pl/sql non autorisée dans ce contexte
Erreurs : consulter le journal du compilateur

**LandGreen** · 06/12/2023, 18h15

Solution

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
type ti_DONN is table of r_DONN index by binary_integer;
riDONN ti_DONN;
rDONN r_DONN;
riDONN_FIN ti_DONN;
rDONN_FIN r_DONN;
 
 
   rDONN.code := cDONN.CLEF;
   rDONN.vale := v_JSON_COLO.get_string(cDONN.CODE_DONN);
   riDONN(riDONN.count+1) := rDONN;
 
 
 
for i in 1..riDONN.count loop
        if instr(riDONN(i).code, '$FUSI$') > 0 then
            if i = 1 then
                rDONN_FIN.code := replace(riDONN(i).code, '$FUSI$','');
                rDONN_FIN.vale := replace(riDONN(i).vale, '$FUSI$','');
            else
                riDONN_FIN(i-1).vale := riDONN_FIN(i-1).vale || replace(riDONN(i).vale, '$FUSI$','');
            end if;
        else
            rDONN_FIN.code := riDONN(i).code;
            rDONN_FIN.vale := riDONN(i).vale;
        end if;
        riDONN_FIN(riDONN_FIN.count+1) := rDONN_FIN;
    end loop;
    --DBMS_OUTPUT.PUT_LINE('vLIGN=' || vLIGN);
    joLIGN := new JSON_OBJECT_T;
    for i in 1..riDONN_FIN.count loop
        joLIGN.put(riDONN_FIN(i).code, riDONN_FIN(i).vale);
    end loop;

**LandGreen** · 07/12/2023, 11h24

Envoyé par skuatamad

A améliorer, mais c'est un début :

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
create or replace type t_keyval as object (
		NAME  VARCHAR2(32),
		VAL VARCHAR2(500)
	);
create or replace type tab_t_keyval is table of t_keyval index by binary_integer;
 
 
create or replace function GET_KEY_VALUES(P_JSON_DOC VARCHAR2)
return tab_t_keyval PIPELINED as	
	JO JSON_OBJECT_T := JSON_OBJECT_T(P_JSON_DOC);
    JO_KEYS JSON_KEY_LIST := JO.get_keys();
	t_rec tab_t_keyval := tab_t_keyval();
	j number := 1;
begin
--
    for i in 1..JO_KEYS.count loop
		if i = 1 then 
			t_rec.extend;
			t_rec(t_rec.last) := t_keyval(replace(JO_KEYS(i),'$FUSI$',''), replace(JO.get_string(JO_KEYS(i)),'$FUSI$',''));
		elsif JO_KEYS(i) like '$FUSI$%' then
			t_rec(j).val  := t_rec(j).val || ' ' ||replace(JO.get_string(JO_KEYS(i)),'$FUSI$','');
		else			
			j := j + 1;
			t_rec.extend;
			t_rec(t_rec.last) := t_keyval(JO_KEYS(i),JO.get_string(JO_KEYS(i)));
		end if;
	end loop;
--
	for i in 1..j loop
		--dbms_output.put_line (t_rec(i).name ||' , '||t_rec(i).val);
        pipe row (t_keyval(t_rec(i).name,t_rec(i).val));
	end loop;
end;
/
 
with t as (
select 1 as id, name, val from TABLE(GET_KEY_VALUES('{"$FUSI$FRAI__TTC":"$FUSI$ 1680000","$FUSI$SALA__PREN":"$FUSI$HADDOCK","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ CAPITAINE"}'))
union all
select 2 as id, name, val from TABLE(GET_KEY_VALUES('{"$FUSI$FRAI__TTC":"$FUSI$ 2030000","$FUSI$SALA__PREN":"$FUSI$JEANETTE","NDF__DATE_CREA":"10-02-2022","$FUSI$SALA__NOM":"$FUSI$ DUJARDIN"}'))
    )
select id, json_objectagg (name,val)
  from t
 group by id;

J'espère que vous allez bien. Je me tourne vers la communauté car j'ai rencontré un petit problème lors de l'exécution d'un code, et je suis convaincu que vos connaissances et votre expérience pourraient m'être d'une grande aide.

Après avoir soigneusement considéré différentes solutions, j'ai finalement opté pour une approche spécifique. Cependant, lors de l'exécution du code, je me heurte à un problème que je n'arrive pas à résoudre malgré mes efforts.

Je sollicite donc votre expertise pour m'apporter des éclaircissements sur cette situation. Si quelqu'un a déjà rencontré un problème similaire ou a des idées sur la manière de le résoudre, je serais reconnaissant de recevoir vos conseils.

Je vous remercie sincèrement par avance pour votre temps et votre assistance. Votre contribution sera d'une grande valeur pour moi dans la résolution de ce problème.

Code :

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

create or replace type tab_t_keyval is table of t_keyval index by binary_integer;

erreur :

Code :

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

1
2
3
4
5
6
 
0/0       PL/SQL: Compilation unit analysis terminated
1/29      PLS-00355: utilisation de la table pl/sql non autorisée dans ce contexte
Erreurs : consulter le journal du compilateur
 
Elément Type TAB_T_KEYVAL compilé

**Waldar** · 07/12/2023, 12h23

Si la données est dans un CLOB, la simple fonction replace doit fonctionner non ?
Suis un peu rouillé avec Oracle DB et je n'ai pas trop suivi les dernières fonctionnalités, mais ceci m'a l'air de fonctionner:

Code :

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

1
2
3
4
5
6
select treat(replace(to_clob('"$FUSI$FRAI__TTC":"$FUSI$ 1773","$FUSI$SALA__PREN":"$FUSI$PARFAIT","NDF__DATE_CREA":"09-03-2022","$FUSI$SALA__NOM":"$FUSI$ MONSIEUR"'), '$FUSI$', '') as json) as tst
  from dual;
 
TST
------------------------------------------------------------------------------------------------
"FRAI__TTC":" 1773","SALA__PREN":"PARFAIT","NDF__DATE_CREA":"09-03-2022","SALA__NOM":" MONSIEUR"

**LandGreen** · 07/12/2023, 13h06

Bonjour et merci pour votre réponse,

cependant la chaine de texte est variable comme j'ai mentionné en haut

**skuatamad** · 07/12/2023, 15h07

C'était à cause du index by binary_integer qui n'a rien à faire là (mauvais copier/coller) :

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
create or replace type t_keyval as object (
		NAME  VARCHAR2(32),
		VAL VARCHAR2(500)
	)
/
create or replace type tab_t_keyval is table of t_keyval
/
 
create or replace function GET_KEY_VALUES(P_JSON_DOC VARCHAR2)
return tab_t_keyval PIPELINED as	
	JO JSON_OBJECT_T := JSON_OBJECT_T(P_JSON_DOC);
    JO_KEYS JSON_KEY_LIST := JO.get_keys();
	t_rec tab_t_keyval := tab_t_keyval();
	j number := 1;
begin
--
    for i in 1..JO_KEYS.count loop
		if i = 1 then 
			t_rec.extend;
			t_rec(t_rec.last) := t_keyval(replace(JO_KEYS(i),'$FUSI$',''), replace(JO.get_string(JO_KEYS(i)),'$FUSI$',''));
		elsif JO_KEYS(i) like '$FUSI$%' then
			t_rec(j).val  := t_rec(j).val || ' ' ||replace(JO.get_string(JO_KEYS(i)),'$FUSI$','');
		else			
			j := j + 1;
			t_rec.extend;
			t_rec(t_rec.last) := t_keyval(JO_KEYS(i),JO.get_string(JO_KEYS(i)));
		end if;
	end loop;
--
	for i in 1..j loop
		--dbms_output.put_line (t_rec(i).name ||' , '||t_rec(i).val);
        pipe row (t_keyval(t_rec(i).name,t_rec(i).val));
	end loop;
end;
/
 
with t as (
select 1 as id, '{"$FUSI$FRAI__TTC":"$FUSI$ 1680000","$FUSI$SALA__PREN":"$FUSI$HADDOCK","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ CAPITAINE"}' as js from dual 
union all
select 2 as id, '{"$FUSI$FRAI__TTC":"$FUSI$ 2030000","$FUSI$SALA__PREN":"$FUSI$JEANETTE","NDF__DATE_CREA":"10-02-2022","$FUSI$SALA__NOM":"$FUSI$ DUJARDIN"}' from dual 
    )
select id, json_objectagg (c.name,c.val)
  from t
 cross apply GET_KEY_VALUES (t.js) c 
 group by id;

**LandGreen** · 07/12/2023, 16h14

Un immense merci à skuatamad pour son soutien exceptionnel !

**JeitEmgie** · 07/12/2023, 18h17

Il doit avoir moyen de générer le SQL en se basant sur JSON_DATAGUIDE et donc de ne pas avoir à se préoccuper d'une liste de colonnes prédéfinies :
le data guide est une array d'object JSON dont l'un des champs est le path.
(avec toutefois un inconvénient : il ne respecte pas l'ordre original... d'où le instr() pour calculer un key_pos qui servira à jouer avec lead() et lag())

Un petit POC :

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
WITH guides(js, guide) AS (
    select 
        q'~{"$FUSI$FRAI__TTC":"$FUSI$ 1680000","$FUSI$SALA__PREN":"$FUSI$HADDOCK","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ CAPITAINE"}~',
        json_dataguide(
            q'~{"$FUSI$FRAI__TTC":"$FUSI$ 1680000","$FUSI$SALA__PREN":"$FUSI$HADDOCK","NDF__DATE_CREA":"08-04-2022","$FUSI$SALA__NOM":"$FUSI$ CAPITAINE"}~'
        )
    from dual
)
SELECT
    'SELECT json_object(' || chr(10)
        || LISTAGG(CASE to_fuse_flag WHEN 0 THEN object_sql END, ', ' || chr(10)) WITHIN group(ORDER BY key_pos)
        || ') FROM data, ' || chr(10)
        || 'json_table(' || chr(10)
        || 'js, ''$''' || chr(10)
        || 'columns (' || chr(10)
        || LISTAGG(columns_sql, ', ' || chr(10)) WITHIN group(ORDER BY key_pos)
        || ')' || chr(10)
        || ')'        
FROM (
    SELECT
        KEY, key_path, key_pos,
        
        '''' || KEY || ''' IS REPLACE(' || KEY || ', ''$FUSI$'', '''')'  ||
        CASE WHEN INSTR(lead(key_path,1) over(ORDER BY key_pos), '$FUSI$') > 0 THEN
            ' || ' || ' REPLACE(' || lead(KEY,1) over(ORDER BY key_pos) || ', ''$FUSI$'', '''')'
        END AS object_sql,
        
        CASE WHEN INSTR(key_path, '$FUSI$') > 0 AND lag(key,1) OVER(ORDER BY key_pos) IS NOT NULL THEN 1 ELSE 0 END AS to_fuse_flag,
        
        KEY || ' PATH ' || '''' || key_path || '''' AS columns_sql
    
    FROM (
        SELECT REPLACE(REPLACE(REPLACE(KEY, '$.', ''),'"',''),'$FUSI$','') AS key,
            KEY AS key_path,
            instr(js, REPLACE(REPLACE(KEY, '$.', ''),'"','')) AS key_pos
        FROM 
            guides,
            json_table(
                guide,
                '$[*]'
                columns (
                    KEY PATH '$."o:path"'
                )
            )
        WHERE key <> '$'
    )
)
;

va vous retourner un SQL:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT json_object(
    'FRAI__TTC' IS REPLACE(FRAI__TTC, '$FUSI$', '') ||  REPLACE(SALA__PREN, '$FUSI$', ''), 
    'NDF__DATE_CREA' IS REPLACE(NDF__DATE_CREA, '$FUSI$', '') ||  REPLACE(SALA__NOM, '$FUSI$', '')) FROM data, 
    json_table(
        js, '$'
        columns (
        FRAI__TTC PATH '$."$FUSI$FRAI__TTC"', 
        SALA__PREN PATH '$."$FUSI$SALA__PREN"', 
        NDF__DATE_CREA PATH '$.NDF__DATE_CREA', 
        SALA__NOM PATH '$."$FUSI$SALA__NOM"')
)
;

prêt à utiliser dans un EXECUTE IMMEDIATE... BULK COLLECT et sans avoir à s'occuper d'une liste de noms prédéfinis...

https://dbfiddle.uk/GBa4Sp2H

Suppression des clés JSON avec une certaine chaîne de caractères en PL/SQL

PL/SQL Oracle

Discussions similaires

Partager

Partager