Forum Stats

  • 3,771,043 Users
  • 2,253,214 Discussions
  • 7,875,716 Comments

Discussions

JSON_OBJECTAGG and ORDER BY

Hi,

COL employes FORMAT A80

WITH cte AS (SELECT  employee_id,first_name,last_name,hire_date

            FROM    employees WHERE employee_id BETWEEN 100 AND 120

         ORDER BY last_name)

SELECT JSON_OBJECTAGG(KEY 'employes' VALUE

  JSON_OBJECT(KEY 'id'      VALUE employee_id,

              KEY 'prenom'  VALUE first_name,

              KEY 'nom'     VALUE last_name,

              KEY 'embauche' VALUE TO_CHAR(hire_date,'YYYY-MM-DD')))

  AS employes

FROM cte;

returns a correct JSON but not ordered.

Replacing by the name of columns of the CTE the result is ordered.

I don't know why?

Best regards

Best Answer

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,934 Red Diamond

    CTE returns rows ordered. Then main query does aggregation which can be done different ways. If sort is involved (e.g. SORT GROUP BY) then CTE ordered data will be re-sorted.

    SY.

  • soutou
    soutou Member Posts: 106 Blue Ribbon

    Thanks Solomon but how do you involve a sort?

    It's right to say that xmlagg, listagg, json_arrayagg allow to sort with an explicit option, option which is still missing to JSON_OBJECTAGG ?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,934 Red Diamond

    Actually query rewrite simply ignores ORDER BY last_name. What you could do is force it:

    WITH cte AS (
                 SELECT  employee_id,first_name,last_name,hire_date
                   FROM  hr.employees WHERE employee_id BETWEEN 100 AND 120
                   ORDER BY ROW_NUMBER() OVER(ORDER BY LAST_NAME)
                )
    SELECT  JSON_OBJECTAGG(
                           KEY 'employes'
                           VALUE JSON_OBJECT(
                                             KEY 'id'       VALUE employee_id,
                                             KEY 'prenom'   VALUE first_name,
                                             KEY 'nom'      VALUE last_name,
                                             KEY 'embauche' VALUE TO_CHAR(hire_date,'YYYY-MM-DD')
                                            )
                          ) AS employees
      FROM  cte
    /
    

    Or:

    WITH cte
      AS (
          SELECT  ROW_NUMBER() OVER(ORDER BY LAST_NAME) RN,
                  JSON_OBJECT(
                              KEY 'nom'      VALUE last_name,
                              KEY 'id'       VALUE employee_id,
                              KEY 'prenom'   VALUE first_name,
                              KEY 'embauche' VALUE TO_CHAR(hire_date,'YYYY-MM-DD')) employee
            FROM  hr.employees
            WHERE employee_id BETWEEN 100 AND 120
            ORDER BY last_name
         )
    SELECT  JSON_OBJECTAGG(
                           KEY 'employees' VALUE employee
                          ) AS employes
      FROM  cte
    /
    

    SY.

  • soutou
    soutou Member Posts: 106 Blue Ribbon

    Thanks Solomon, last question

    It's right to say that xmlagg, listagg, json_arrayagg allow to sort with an explicit option, option which is still missing to JSON_OBJECTAGG ?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,934 Red Diamond
    Accepted Answer