sponsored links

Collapse

Announcement

Collapse
No announcement yet.

JSON_TABLE starting with an array

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • JSON_TABLE starting with an array

    Hi all,

    I need some help to parse a json than start with square brackets, something like this:

    Code:
    [
    {
    "LEI": {
    "$": "894800UN8CML2Q7Y4V11"
    },
    "Entity": {
    "LegalName": {
    "$": "PAOLINO PAPERINO"
    },
    "LegalAddress": {
    "@xml:lang": "",
    "FirstAddressLine": {
    "$": "VIA ROMA 16"
    },
    "City": {
    "$": "TORINO"
    },
    "Region": {
    "$": "IT-21"
    },
    "Country": {
    "$": "IT"
    },
    "PostalCode": {
    "$": "10100"
    }
    },
    "HeadquartersAddress": {
    "@xml:lang": "",
    "FirstAddressLine": {
    "$": "VIA ROMA 16"
    },
    "City": {
    "$": "TORINO"
    },
    "Region": {
    "$": "IT-21"
    },
    "Country": {
    "$": "IT"
    },
    "PostalCode": {
    "$": "10100"
    }
    },
    "RegistrationAuthority": {
    "RegistrationAuthorityID": {
    "$": "RA000407"
    },
    "RegistrationAuthorityEntityID": {
    "$": "TO-1070547"
    }
    },
    "LegalJurisdiction": {
    "$": "IT"
    },
    "LegalForm": {
    "EntityLegalFormCode": {
    "$": "8888"
    },
    "OtherLegalForm": {
    "$": "srl"
    }
    },
    "EntityStatus": {
    "$": "ACTIVE"
    }
    },
    "Registration": {
    "InitialRegistrationDate": {
    "$": "2018-06-19T09:24:09+02:00"
    },
    "LastUpdateDate": {
    "$": "2019-06-20T02:30:09+02:00"
    },
    "RegistrationStatus": {
    "$": "LAPSED"
    },
    "NextRenewalDate": {
    "$": "2019-06-19T09:24:10+02:00"
    },
    "ManagingLOU": {
    "$": "529900F6BNUR3RJ2WH29"
    },
    "ValidationSources": {
    "$": "FULLY_CORROBORATED"
    },
    "ValidationAuthority": {
    "ValidationAuthorityID": {
    "$": "RA000407"
    },
    "ValidationAuthorityEntityID": {
    "$": "TO-1070547"
    }
    }
    }
    }
    ]
    I have it in a clob columns, and I try to parse it with json_table:

    Code:
    Select LEI, outputclob
      From Uhttpbch0f, 
            Json_Table (OutPutClob format json, 'lax $' 
                Columns (Scadenza Varchar(30) Path 'lax $.LEI."$"')) As Lei
      Where Rowid = 9826;
    If I remove the first square brackets it works, but how can I do the parse with the square brackets?
    Many thanks.
    Bye

  • #2
    There's a mistake on the sql:

    the is the corret sql, but it down't work too:

    Code:
     
     Select LEI, outputclob   From Uhttpbch0f,          Json_Table (OutPutClob format json, 'lax $'              Columns (Lei Varchar(30) Path 'lax $.LEI."$"')) As Lei   Where Rowid = 9826;

    Comment


    • #3
      Unfortunately JSON_TABLE cannot schred a JSON document starting with an array...
      But there is a work arround:
      Just embedd the JSON document in an Object and voila you can shred it.
      Code:
      select Lei.*, OutputClob
      from Uhttpbch0f,
           JSON_TABLE('{"root": ' concat OutPutClob concat '}' Format JSON,
                      '$.root[*]'
                      Columns (Scadenza Varchar(256) Path 'lax $.LEI."$"')) As Lei;

      Comment


      • #4
        Hi Birgitta,

        many thanks for your help, but I'm still have a problem,

        If I update my column outputclob with the value: outputclob = '{"root": ' concat OutPutClob concat '}'
        it works.

        But if I try as you said me I'm getting the error:

        Bad ByteLen passed to GETSPACE: 00016773504.

        My outputclob column is about 2GB.

        Any other workaround?
        Many thanks.

        Comment


        • #5
          Put a RTRIM around OutPutClob

          Birgitta

          Comment

          sponsored links

          Collapse

          Working...
          X