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

Oralce JSON_TRANSFORM, set/insert/append not working

Why this:

with j(jval) AS
(
select JSON_SERIALIZE('{"contract" :{"contract-type" : "P",
    "contract-id" : 123451,
    "proposal-no" : "123456",
    "serial-no" : "serialasd922",
    "product-code" : "s093",
    "contract-sign-dt" : "2016-12-01T00:00:00",
    "contract-start-dt" : "2016-12-09T00:00:00"
  }
}' pretty) from dual
)
SELECT   json_transform(jval, insert '$.contract[*].x' = '50' pretty)
FROM     j

is not working? I’m Trying to insert new value under $.contract.
Set with insert on missing also not working, but when it comes to SET to update existing field – everything’s fine.

Tags:

2

Answers


  1. 0

    You are trying to insert '$.contract[*].x', but contract is an object not an array, so the [*] shouldn’t be there.

    Use insert '$.contract.x' = 50 instead.

    with j(jval) AS
(
select JSON_SERIALIZE('{"contract" :{"contract-type" : "P",
    "contract-id" : 123451,
    "proposal-no" : "123456",
    "serial-no" : "serialasd922",
    "product-code" : "s093",
    "contract-sign-dt" : "2016-12-01T00:00:00",
    "contract-start-dt" : "2016-12-09T00:00:00"
  }
}' pretty) from dual
)
SELECT   json_transform(jval, insert '$.contract.x' = '50' pretty)
FROM     j
    JSON_TRANSFORM(JVAL,INSERT’$.CONTRACT.X’=’50’PRETTY)
    {
      "contract" :
      {
        "contract-type" : "P",
        "contract-id" : 123451,
        "proposal-no" : "123456",
        "serial-no" : "serialasd922",
        "product-code" : "s093",
        "contract-sign-dt" : "2016-12-01T00:00:00",
        "contract-start-dt" : "2016-12-09T00:00:00",
        "x" : "50"
      }
    }

    fiddle

    I’m not sure why set works with or without the [*].

    Login or Signup to reply.
  2. 0

    I don’t have an Oracle 19 server to test on but in Oracle 21, you want $.contract.x and not $.contract[*].x:

    SELECT   json_transform(jval, insert '$.contract.x' = '50' pretty)
FROM     j

    Which, for the sample data:

    CREATE TABLE j (jval CLOB CHECK (jval IS JSON));

INSERT INTO j (jval) VALUES (
  JSON_SERIALIZE(
   '{"contract" :{
    "contract-type" : "P",
    "contract-id" : 123451,
    "proposal-no" : "123456",
    "serial-no" : "serialasd922",
    "product-code" : "s093",
    "contract-sign-dt" : "2016-12-01T00:00:00",
    "contract-start-dt" : "2016-12-09T00:00:00"
  }
}'
    pretty
  )
);

    Outputs:

    JSON_TRANSFORM(JVAL,INSERT’$.CONTRACT.X’=’50’PRETTY)
    {
      "contract" :
      {
        "contract-type" : "P",
        "contract-id" : 123451,
        "proposal-no" : "123456",
        "serial-no" : "serialasd922",
        "product-code" : "s093",
        "contract-sign-dt" : "2016-12-01T00:00:00",
        "contract-start-dt" : "2016-12-09T00:00:00",
        "x" : "50"
      }
    }

    To get $.contract[*].x to work then contract would need to be an array of objects that looked like {"contract" :[{},{}]} and then after:

    SELECT   json_transform(jval, insert '$.contract[*].x' = '50' pretty)
FROM     j

    The output would be:

    JSON_TRANSFORM(JVAL,INSERT’$.CONTRACT[*].X’=’50’PRETTY)
    {
      "contract" :
      [
        {
          "x" : "50"
        },
        {
          "x" : "50"
        }
      ]
    }

    fiddle

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

Back To Top
Search