Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT ID,
       NOM,
       ( SELECT LISTAGG(prenom, ', ')
         FROM   JSON_TABLE(
                  PRENOMS,
                  '$[*]'
                  COLUMNS (prenom VARCHAR2(200) PATH '$')
                )
       ) AS prenoms,
       DATE_NAISS
FROM   JSON_TABLE('[
  {
    "Id": 123,
    "Nom": "Toto",
    "Prenoms": [ "Pascal", "Vincent" ],
    "DateNaissance": "1983-01-01T00:00:00"
  }
]',
        '$[*]'
        COLUMNS (
          ID         NUMBER           PATH '$.Id',
          NOM        VARCHAR2(200)    PATH '$.Nom',
          PRENOMS    CLOB FORMAT JSON PATH '$.Prenoms',
          DATE_NAISS DATE             PATH '$.DateNaissance'
        )
      );


ID
NOM
PRENOMS
DATE_NAISS


123
Toto
Pascal, Vincent
1983-01-01 00:00:00


SELECT JSON_ARRAYAGG(
         JSON_OBJECT(
           KEY 'Id' VALUE ID,
           KEY 'Nom' VALUE NOM,
           KEY 'Prenoms' VALUE (
             SELECT LISTAGG(prenom, ', ')
             FROM   JSON_TABLE(
                      PRENOMS,
                      '$[*]'
                      COLUMNS (prenom VARCHAR2(200) PATH '$')
                    )
           ),
           KEY 'DateNaissance' VALUE DATE_NAISS
         )
       ) AS json
FROM   JSON_TABLE('[
  {
    "Id": 123,
    "Nom": "Toto",
    "Prenoms": [ "Pascal", "Vincent" ],
    "DateNaissance": "1983-01-01T00:00:00"
  }
]',
        '$[*]'
        COLUMNS (
          ID         NUMBER           PATH '$.Id',
          NOM        VARCHAR2(200)    PATH '$.Nom',
          PRENOMS    CLOB FORMAT JSON PATH '$.Prenoms',
          DATE_NAISS DATE             PATH '$.DateNaissance'
        )
      );


JSON


[{"Id":123,"Nom":"Toto","Prenoms":"Pascal, Vincent","DateNaissance":"1983-01-01T00:00:00"}]