select range of element from array json

Question

SELECT JSON_EXTRACT(data, '$.rows[*]') AS name from my_table ;

the data col is json col and contain rows as an array element need to select a range of element from row [like a pagination ]

try to make pagination from DB direct to select for example 1st page with size 25 then 2nd page but start from the last of 1st one and son.

id	data
1	{"rows":[{"name":"name_1"},{"name":"name_2"},{"name":"name_3"},{"name":"name_4"}]}

Answer 1

Here's a solution:

SELECT JSON_OBJECT('rows', JSON_ARRAYAGG(JSON_OBJECT('name', t.name))) AS data
FROM (
  SELECT j.name
  FROM my_table
  CROSS JOIN JSON_TABLE(my_table.data, '$.rows[*]' COLUMNS (
      ord FOR ORDINALITY,
      name VARCHAR(20) PATH '$.name'
    )
  ) AS j
  WHERE j.ord BETWEEN 1 and 25
) AS t;

Use different numbers for the BETWEEN arguments to get other "pages" of your array.

If this seems too complex, then you should be storing data in normal rows and columns instead of JSON.