Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5728

JSON production functions JSONOBJECT and JSONARRAY_AGG producing CLOBs

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 12.2
    • None
    • Query Engine
    • None

    Description

      JSON production functions like JSONOBJECT and JSONARRAY_AGG seem to be still returning a clob object rather than a 'json' object even though Teiid has the first class data type for json now.

      for ex:

      CREATE VIRTUAL PROCEDURE CustomerJSON(IN p1 integer) RETURNS TABLE (json_out json) 
  OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'CustomerJson/{p1}') AS
BEGIN
    SELECT JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(SSN, Name))) AS json_out FROM accounts.customer;
END

      would fail with 

      Caused by: org.teiid.deployers.VirtualDatabaseException: TEIID40095 TEIID31080 virt.CustomerJSON validation error: TEIID31121 The expected result set of the procedure virt.CustomerJSON does not match the result set from returnable statement SELECT JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(SSN, Name))) AS json_out FROM accounts.customer; use WITHOUT RETURN to indicate the statement should not be returned - The definition for virt.CustomerJSON has the wrong type for column 1.  Expected json, but was clob.
	at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:845) ~[teiid-runtime-12.2.0-SNAPSHOT.jar:12.2.0-SNAPSHOT]
	at org.teiid.spring.autoconfigure.TeiidServer.deployVDB(TeiidServer.java:315) ~[classes/:na]
	... 17 common frames omitted

      Attachments

        Issue Links

          relates to

          Feature Request - Feature requests from customers and/or users TEIID-5499 Add a json type

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Resolved

          Activity

            People

              rhn-engineering-shawkins Steven Hawkins
              rhn-engineering-rareddy Ramesh Reddy
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: