JSON_OBJECTAGG and ORDER BY

soutou

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

Solomon Yakobson

Yes.

SY.

Solomon Yakobson

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

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

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

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

Yes.

SY.