Discussion :

[LandGreen]

Bonjour à tous,

Je fais face à un défi et j'aurais besoin de votre expertise pour le résoudre.
J'ai une table dans ma base de données (MA_TABLE) avec une colonne nommée LIGN de type CLOB où les données sont stockées au format JSON.
Mon objectif est de créer une vue qui récupère l'ensemble clé/valeur avec les valeurs en ligne et les clés en colonne.

La particularité est que le nombre de clés/valeurs est dynamiquement construit. Par exemple, pour un ID_LIGN=1, je pourrais avoir 5 paires clé/valeur, tandis que pour un autre ID_LIGN=2, j'en aurais peut-être 7.

J'utilise Oracle Database, et je pense que la fonction JSON_TABLE pourrait être utile, mais je ne suis pas sûr de la meilleure approche pour gérer la dynamique des clés/valeurs.

Si quelqu'un a déjà travaillé sur un problème similaire ou a des suggestions sur la manière de créer cette vue de manière efficace, je serais reconnaissant de toute aide que vous pourriez fournir.

Merci d'avance pour vos conseils !

Code :

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

1
2
3
4
5
6
7
 
ID_LIGN 1
{"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}
ID_LIGN 2
{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","FRAI__DATE_DEPL":"26/08/2021 00:00:00","FRAI__TTC":38.8}
ID_LIGN 3
{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","NDF__DATE_BASC":"10/02/2022 19:25:00","FRAI__TTC":203}

[JeitEmgie]

Regardez json_dataguide() qui vous permet d'obtenir le "meta" JSON d'un autre... vous utiliserez json_table() pour interpréter le contenu du data guide et pourrez générer le SQL spécifique à chaque CLOB, mais il faudra ensuite exécuter ce SQL en EXECUTE IMMEDIATE, donc la solution sera en PL/SQL. (voir https://www.developpez.net/forums/d2...cteres-pl-sql/ et https://dbfiddle.uk/GBa4Sp2H)


Soit:

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
CREATE TABLE t_lign
(
	id number(10) PRIMARY key,
	lign CLOB
);
 
 
INSERT INTO t_lign
select 1, q'~{"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}~' 
from dual union all
select 2, q'~{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","FRAI__DATE_DEPL":"26/08/2021 00:00:00","FRAI__TTC":38.8}~'
from dual union all
select 3, q'~{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","NDF__DATE_BASC":"10/02/2022 19:25:00","FRAI__TTC":203}~'
from dual
;
 
 
CREATE TYPE lignresul_t AS OBJECT (
	id number(10),
	KEY varchar2(64),
	value varchar2(64)
);
/
 
 
CREATE TYPE lignresul_tt AS TABLE OF lignresul_t ;
/
 
 
CREATE OR REPLACE FUNCTION convert_lign_json
RETURN lignresul_tt PIPELINED
IS
	v_id	NUMBER(10);
	v_key	VARCHAR2(64);
	v_value	VARCHAR2(64);
	v_result lignresul_tt;
BEGIN
 
	FOR rec IN (
		with guides(id, guide) as (
			select id, json_dataguide(lign,dbms_json.format_flat, DBMS_JSON.PRETTY) 
			from t_lign 
			group by id
		)
		select 
			'SELECT lignresul_t(' || id || ', ''' || REPLACE(k,'$.','') || ''', json_value(lign, ''' || k || ''') ) FROM t_lign WHERE id = ' || id AS script 
		from guides,
		json_table(
			guide,
			'$[*]'
			columns (
				k PATH '$."o:path"'
			)
		)
		WHERE k <> '$'
	)
	LOOP
		EXECUTE IMMEDIATE rec.script BULK COLLECT INTO v_result ;	
		FOR i IN v_result.first .. v_result.last
		LOOP
			PIPE ROW(v_result(i));
		END LOOP;
	END LOOP
	;
END ;
/
 
 
SELECT * FROM CONVERT_LIGN_JSON() ;

NB
si lign est déclaré JSON au lieu de CLOB cela ne marchera pas car json_guide n'acceptera un JSON avec plusieurs fois la même clé.