Question 136949 in 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

Print value from json blob in oracle database

Within a table (prov) in my database, I have a column (data_json) of type BLOB that contains data of type JSON.
The JSON structure has two types (can be more but now only two).

For the current two types, I am trying to print a specific value which is the type within the entity.

Example of the first json (type1):

{
    "agent": {
        "iss:02228ba5-554d-4db7-802b-89ff360f2315": {
            "iss:idcode": "000005",
            "idd:type": "idd:org"
        }
    },
    "entity": {
        "iss:754df246-e3f7-46f6-b53c-f6f2770177f6": {
            "iss:algoritme1": "d5ad30e753204063bf15aea24805d2c3",
            "idd:type": "type1",
            "iss:algoritme2": "20ea978f31a14c7bac1415a9d3a50195",
            "iss:identifier": "test1"
        }
    }
}

Example of the secound json (type2):

{
    "entity" : {
        "iss:132b7a2c-e598-419a-a3a8-6adb4aa86d6b" : {
            "idd:type" : [ "type2" ],
            "iss:identifier" : [ "test2" ]
        },
        "iss:fc36e29c-8ce9-4f3e-a8f9-fa4b32d5d2f0" : {
            "idd:value" : [ "23aeb0dd598f49c9b8fd065196724220" ],
            "iss:algoritme" : [ "algoritme1" ],
            "idd:type" : [ "tdd" ]
        },
        "iss:b53ae8ca-df09-4a66-9727-6f8a9bf1afcb" : {
            "idd:value" : [ "65d3d05ce8bc4a35b2a5dd96e377a3d8" ],
            "iss:algoritme" : [ "algoritme2" ],
            "idd:type" : [ "tdd" ]
        }
    },
    "agent" : {
        "iss:818c5dff-f08d-4831-b750-4887a10f1a50" : {
            "idd:type" : [ { "type" : "idd:NAME", "$" : "idd:org" } ],
            "iss:idcode" : [ "000005" ]
        }
    }
}

With the following query

SELECT id,
       JSON_VALUE(data_json, '$.entity.*."idd:type"[0]') type,
       data_json
FROM prov p;

I am getting the right output for the type1 but it’s null for the type2 (It supposed to be type2)

ID TYPE DATA_JSON
4472 type1 (BLOB)
4792 (null) (BLOB)

Any idea about what is the problem here?

update———————————————-

The second json (type2) can come in different order or with more elements within the entity:
but the "idd:type" : [ "type2" ] will be present only one time but also it can be type3 at some point.

{
    "entity" : {
        "iss:fc36e29c-8ce9-4f3e-a8f9-fa4b32d5d2f0" : {
            "idd:value" : [ "23aeb0dd598f49c9b8fd065196724220" ],
            "iss:algoritme" : [ "algoritme1" ],
            "idd:type" : [ "tdd" ]
        },
        "iss:all9829c-8ce9-4fe3-a9a9-fa4b35a7d2f0" : {
            "idd:value" : [ "23aeb0dd598f49c9b8fd065196724220" ],
            "iss:algoritme" : [ "algoritme2" ],
            "idd:type" : [ "tdd" ]
        },
        "iss:132b7a2c-e598-419a-a3a8-6adb4aa86d6b" : {
            **"idd:type" : [ "type2" ],**
            "iss:identifier" : [ "test2" ]
        },
        "iss:b53ae8ca-df09-4a66-9727-6f8a9bf1afcb" : {
            "idd:value" : [ "65d3d05ce8bc4a35b2a5dd96e377a3d8" ],
            "iss:algoritme" : [ "algoritme3" ],
            "idd:type" : [ "tdd" ]
        }
    },
    "agent" : {
        "iss:818c5dff-f08d-4831-b750-4887a10f1a50" : {
            "idd:type" : [ { "type" : "idd:NAME", "$" : "idd:org" } ],
            "iss:idcode" : [ "000005" ]
        }
    }
}
Tags:

2

Answers


  1. 0

    This is because

    The SQL/JSON function JSON_VALUE finds a specified scalar JSON value in JSON data and returns it as a SQL value.

    But $.entity.*."idd:type"[0] points to an array of strings. You need to use json_query for this:

    select
  id,
  json_query(
    data_json,
    '$.entity.*."idd:type"[0]'
    with conditional array wrapper
  )as type_
from prov p;
    ID TYPE_
    4472 "type1"
    4792 ["type2","tdd","tdd"]

    You may extract the first element of this array using another json_value function:

    select
  id,
  json_value(json_query(
    data_json,
    '$.entity.*."idd:type"[0]'
    with conditional array wrapper
  ), '$[0]') as type_
from prov p;
    ID TYPE_
    4472 type1
    4792 type2

    fiddle

    Login or Signup to reply.
  2. 0

    With your second JSON you have multiple iss:* values, and thus multiple idd:type values within them. Your query returns NULL ON ERROR by default, so the result you see is null:

    SELECT id,
       JSON_VALUE(data_json, '$.entity.*."idd:type"[0]') type
FROM prov p;
    ID TYPE
    4472 type1
    4792 null

    But if you add the ERROR ON ERROR then you see:

    SELECT id,
       JSON_VALUE(data_json, '$.entity.*."idd:type"[0]' ERROR ON ERROR) type
FROM prov p;

    ORA-40470: JSON_VALUE evaluated to multiple values

    As the iss:* elements are independent and not part of an array I don’t think you can really target one of them. You can’t, for example, do '$.entity.*."idd:type"[0]' – that gets the same error.

    You could instead use JSON_TABLE to get all of the type values:

    SELECT p.id,
       j.type
FROM prov p
CROSS APPLY JSON_TABLE (
  p.data_json,
  '$.entity.*."idd:type"'
  COLUMNS (
    type PATH '$[*]'
  )
) j
    ID TYPE
    4472 type1
    4792 type2
    4792 tdd
    4792 tdd

    … and if you only want the ‘first’ one you can add and filter on an ORDINALITY value:

    SELECT p.id,
       j.type
FROM prov p
CROSS APPLY JSON_TABLE (
  p.data_json,
  '$.entity.*."idd:type"'
  COLUMNS (
    rn FOR ORDINALITY,
    type PATH '$[*]'
  )
) j
WHERE j.rn = 1
    ID TYPE
    4472 type1
    4792 type2

    Which assumes the type value you care about is always going to be the first one in the parent entity element, of course.

    Or, pick the one that has an iss:identifier as well, if that is a valid thing to check for (I’m just guessing from the sample data):

    SELECT p.id,
       j.type
FROM prov p
CROSS APPLY JSON_TABLE (
  p.data_json,
  '$.entity.*'
  COLUMNS (
    type PATH '$."idd:type"[*]',
    identifier PATH '$."iss:identifier"[*]'
  )
) j
WHERE j.identifier is not null
    ID TYPE
    4472 type1
    4792 type2

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search