Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSON datas to Database, Mapping Json with field contains array[string], transform to string

I use this code to record a JSON datas to a Oracle Database

SELECT NOM, PRENOMS, DATE_NAISS
    FROM JSON_TABLE('[
        {
            "Id": 123,
            "Nom": "Toto",
            "Prenoms": [
                "Pascal",
                "Vincent"
            ],
            "DateNaissance": "1983-01-01T00:00:00"
        }]', '$[*]' COLUMNS 
             (
               "NOM"        PATH '$.Nom',
               "PRENOMS"    PATH '$.Prenoms',
               "DATE_NAISS" DATE PATH  '$.DateNaissance'
             ) ) T_JSON;

and i want to concat the elements of field "Prenoms" like this

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

could you help me, please ?

thanks

"PRENOMS" varchar2(100) FORMAT JSON PATH ‘$."Prenoms"[*]’

the Code i try but it’s not ok, that’s return empty string

Tags:

2

Answers


  1. 0

    Extract the prenoms array as an array and then split it separately and then aggregate it into your desired format:

    SELECT 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 (
          "NOM"        VARCHAR2(200)    PATH '$.Nom',
          "PRENOMS"    CLOB FORMAT JSON PATH '$.Prenoms',
          "DATE_NAISS" DATE             PATH  '$.DateNaissance'
        )
      );

    Which outputs:

    NOM PRENOMS DATE_NAISS
    Toto Pascal, Vincent 1983-01-01 00:00:00

    If you want it back in JSON format then:

    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'
        )
      );

    Which outputs:

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

    fiddle

    Login or Signup to reply.
  2. 0

    You can also do it using nested path:

            '$[*]' COLUMNS 
         (
            id  PATH '$.Id',
           NOM        PATH '$.Nom',
           DATE_NAISS DATE PATH  '$.DateNaissance',
           nested path '$.Prenoms[*]'
           columns (
            ord for ordinality,
            PRENOM    PATH '$'
           )
         )

    and then

    listagg(PRENOM, ', ') within group(order by ord) as prenoms
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search