Good day. The question almost the same as topic subject. So, if I have a query:

select t.* from mytable m,
            json_table
            (m.json_col,'$.arr[*]'
             columns(...)
            ) t
      where m.id = 1

should I bother with order of rows?

TIA, Andrew.

CodePudding user response：

As with many things to do with databases, there is a difference between the theoretical and the practical and the practical at large scales:

• In theory, a result set is an unordered set of rows unless you have specified an ORDER BY clause.
• In practice, for small data sets, the query will be handled by a single process and will generate rows in the order the rows are read from the data file and then processed; which means that it will read a row from mytable and then process the JSON data in order and produce the rows in the same order as the array.
• In practice at larger scales, the query may be handled by multiple processes on a parallel system (among other factors that may affect the order in which results are generated) where each process reads part of the data set and processes it and then the outputs are combined into a single result set. In this case, there is no guarantee which part of the parallel system will provide the next row and a consistent order cannot be guaranteed.

If you want to guarantee an order then use a FOR ORDINALITY column to capture the array order and then use an ORDER BY clause:

SELECT m.something,
       t.*
FROM   mytable m
       CROSS APPLY JSON_TABLE(
         m.json_col,
         '$.arr[*]'
         COLUMNS(
           idx   FOR ORDINALITY,
           value NUMBER PATH '$'
         )
       ) t
WHERE  m.id = 1
ORDER BY m.something, t.idx

Which, for the sample data:

CREATE TABLE mytable (
  id        NUMBER,
  something VARCHAR2(10),
  json_col  CLOB CHECK(json_col IS JSON)
);

INSERT INTO mytable(id, something, json_col)
SELECT 1, 'AAA', '{"arr":[3,2,1]}' FROM DUAL UNION ALL
SELECT 1, 'BBB', '{"arr":[17,2,42,9]}' FROM DUAL;

Outputs:

SOMETHING	IDX	VALUE
AAA	1	3
AAA	2	2
AAA	3	1
BBB	1	17
BBB	2	2
BBB	3	42
BBB	4	9

db<>fiddle here db<>fiddle here