3 Replies Latest reply on Mar 26, 2018 5:04 PM by dmcmahon-Oracle

    JSON _ARRAYAGG is ignoring DISTINCT

    ifernand

      Hi.

       

      I am facing a weird result from SQL/JSON using JSON_ARRAYAGG. I have found this situation on "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" but I have reproduced same results in LiveSQL with "Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production".

       

      I have created a table with 2 columns and populated with 3 rows:

      CREATE table items

      (

        item_id          NUMBER(5) not null,

        item             VARCHAR2(6) not null

      )

      ;

       

      insert into items (item_id, item)

      values (1, '397850')

      ;

       

      insert into items (item_id, item)

      values (2, '397855')

      ;

       

      insert into items (item_id, item)

      values (3, '397855')

      ;

       

      Item_id has 3 different values, but item only have 2 unique values. If I look for different values of item I only find 2 values:

      SELECT DISTINCT item

        FROM items;

       

      ITEM
      397855
      397850

       

      If I convert items into JSON objects I have 2 rows/values:

       

      SELECT   JSON_OBJECT(

                     'item_obj' IS JSON_OBJECT(

                                     'item' IS item))

                    AS qry_result

            FROM (

                   SELECT DISTINCT item

                      FROM items

                     );

       

      QRY_RESULT
      {"item_obj" : {"item" : "397855"}}
      {"item_obj" : {"item" : "397850"}}

       

      But when I try to aggregate this objects as an JSON array I find this:

       

      SELECT JSON_ARRAYAGG( 

                   JSON_OBJECT( 

                     'item_obj' IS JSON_OBJECT( 

                                     'item' IS item)) 

                   FORMAT JSON 

                   ORDER BY item 

                   RETURNING CLOB 

                   ) 

                    AS qry_result 

            FROM ( 

                   SELECT DISTINCT item 

                      FROM items

                     ) ;

       

      QRY_RESULT

      [

      {"item_obj" : {"item" : "397850"}},

      {"item_obj" : {"item" : "397855"}},

      {"item_obj" : {"item" : "397855"}}

      ]

       

      Can someone explain me why this query is returning 3 JSON objects instead of 2? Have I misunderstood something about JSON_ARRAYAGG? Is this a bug?

       

      Regards.

        • 1. Re: JSON _ARRAYAGG is ignoring DISTINCT
          ifernand

          If I use a GROUP BY instead of DISTINCT the query is returning only 2 elements:

           

          SELECT JSON_ARRAYAGG(

                      JSON_OBJECT(

                        'item_obj' IS JSON_OBJECT(

                                        'item' IS item))

                      FORMAT JSON

                      ORDER BY item

                      RETURNING CLOB

                      )

                        AS qry_result

                FROM (

                      SELECT item

                          FROM items

                      GROUP BY item

                        );

           

          QRY_RESULT

          [

          {"item_obj" : {"item" : "397850"}},

          {"item_obj" : {"item" : "397855"}}

          ]

           

          Regards.

          • 2. Re: JSON _ARRAYAGG is ignoring DISTINCT
            Beda Hammerschmidt-Oracle

            Hello,

            this looks like a bug to me - I will confirm with the developer of the feature.

            The problem is that the SELECT DISTINCT subquery is getting merged with the outer JSON_ARRAYAGG query.

            As a consequence the DISTINCT feature is lost.

             

            You can prevent the queries to be merged by adding a NO_MERGE hint as follows

             

            SELECT JSON_ARRAYAGG(

                         JSON_OBJECT(

                           'item_obj' IS JSON_OBJECT(

                                           'item' IS item))

                         FORMAT JSON

                         ORDER BY item

                         RETURNING CLOB

                         )

                          AS qry_result

                  FROM (

                         SELECT /*+ no_merge */ DISTINCT item

                            FROM items

                           ) ;

              2    3    4    5    6    7    8    9   10   11   12   13 

            QRY_RESULT

            --------------------------------------------------------------------------------

            [{"item_obj":{"item":"397850"}},{"item_obj":{"item":"397855"}}]

            • 3. Re: JSON _ARRAYAGG is ignoring DISTINCT
              dmcmahon-Oracle

              I've filed bug 27757725 for this issue. That said, I'm not sure it's a bug, it seems to be a limitation of the aggregation framework. Here's a simple example:

              create table ANIMALS(NAME varchar2(30));
              insert into ANIMALS(NAME) values ('cat');
              insert into ANIMALS(NAME) values ('dog');
              insert into ANIMALS(NAME) values ('cat');
              commit;
              select LISTAGG(NAME,',') within group (order by NAME) from ANIMALS;

              The LISTAGG operator works on groups and it seemingly doesn't support the DISTINCT keyword in the 12.2 release. I tried this example:

              select LISTAGG(NAME,',') from (select DISTINCT NAME from ANIMALS);

              It doesn't appear to be allowed in 12.2 (it appears to work in later releases of the RDBMS). The very similar JSON construct is allowed in 12.2 but ignores the keyword:

              select JSON_ARRAYAGG(NAME) from (select DISTINCT NAME from ANIMALS);
              select JSON_OBJECTAGG('name' value NAME) from (select DISTINCT NAME from ANIMALS);

              The syntax of the JSON operators doesn't make it as clear that a grouping operation is being done, and therefore certain aspects of an in-line view aren't having the effect you might expect. Another example is ORDER BY, which is also ignored because of the aggregating operation above. Here I'm using LISTAGG syntax from a later release so that it's a bit easier to compare the operators side by side:

              select LISTAGG(NAME,',') from (select NAME from ANIMALS order by NAME);
              select JSON_ARRAYAGG(NAME) from (select NAME from ANIMALS order by NAME);

              These operators basically ignore the ORDER BY clauses because they're re-aggregating. The JSON_ARRAYAGG supports an ORDER BY but you have to put it into the operator itself:

              select JSON_ARRAYAGG(NAME order by NAME) from (select NAME from ANIMALS);

              For now, you'll have to use the syntax suggested by Beda to force the in-line view to be run separately. We'll take a look to see if the operators can be enhanced. LISTAGG seems to have been in a later release. It's not clear such an enhancement can/will be backported to 12.2.