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

Implement JSON_EXTRACT function

    XMLWordPrintableJSON

    Details

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

      Description

      JSON_EXTRACT(json_docpath[, path] ...)

      Returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.

      The return value consists of all values matched by the path arguments. If it is possible that those arguments could return multiple values, the matched values are autowrapped as an array, in the order corresponding to the paths that produced them. Otherwise, the return value is the single matched value.

      Example Sql:

      SELECT JSON_EXTRACT(v, '$') AS c1
      ,JSON_EXTRACT(v, '$.b') AS c2
      ,JSON_EXTRACT(v, '$.a[0]') AS c3
      ,JSON_EXTRACT(v, '$.a[0]','$.b[0]') AS c4
      FROM (VALUES ('{"a": [10, true],"b": [11]}')) AS t(v)
      limit 10

      Result:

      c1 c2 c3 c4
      {"a": [10, true],"b": "[10, true]"} [11 10 [10,11]

        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 - 2h
                  2h