Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2867 JSON support
  3. CALCITE-2884

Implement JSON_INSERT, JSON_REPLACE, JSON_SET

    XMLWordPrintableJSON

    Details

    • Type: Sub-task
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      JSON_INSERT(jsondocpathval[, path, val] )

      JSON_REPLACE(jsondocpathval[, path, val] )

      JSON_SET(jsondocpathval[, path, val] )

      Inserts data into a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a  or }}{{* wildcard.

      The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

      A path-value pair for an existing path in the document is ignored and does not overwrite the existing document value. A path-value pair for a nonexisting path in the document adds the value to the document if the path identifies one of these types of values:

      • A member not present in an existing object. The member is added to the object and associated with the new value.
      • A position past the end of an existing array. The array is extended with the new value. If the existing value is not an array, it is autowrapped as an array, then extended with the new value.

      Otherwise, a path-value pair for a nonexisting path in the document is ignored and has no effect.

      For a comparison of JSON_INSERT()JSON_REPLACE(), and JSON_SET(), see the discussion of JSON_SET().

      JSON_INSERT SQL:

      SELECT JSON_INSERT(v, '$.a', 10, '$.c', '[true, false]') AS c1
       FROM (VALUES ('{ "a": 1, "b": [2, 3]}')) AS t(v);

      Result:

      c1
      {"a": 1, "b": [2, 3], "c": "[true, false]"}

      JSON_REPLACE SQL:

      SELECT JSON_REPLACE(v, '$.a', 10, '$.c', '[true, false]') AS c1
       FROM (VALUES ('{ "a": 1, "b": [2, 3]}')) AS t(v);

      Result:

      c1
      {"a": 10, "b": [2, 3],}

      JSON_SET SQL:

      SELECT JSON_INSERT(v, '$.a', 10, '$.c', '[true, false]') AS c1
       FROM (VALUES ('{ "a": 1, "b": [2, 3]}')) AS t(v);

      Result:

      c1
      {"a": 10, "b": [2, 3], "c": "[true, false]"}

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                x1q1j1 Forward Xu
                Reporter:
                x1q1j1 Forward Xu
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 8h
                  8h