oracle - fetch first n json objects from json array

Question

I have a table in which one column we have json array data. in some rows this json is very big (with 10000+ json objects) like below. wanted to know is there any way just to select first 250 objects from the array-

[
   {
      "product":"Vegetable",
      "name":"Potato",
      "price":"$60.00"
   },
   {
      "product":"Fruit",
      "name":"Mango",
      "price":"$3.30"
   },
   {
      "product":"Milk",
      "name":"Milk",
      "price":"$1.08"
   },
.....10,000
]

What is the name of the column? What is the datatype of the column? What is the name of the database table that the column is in? — Abra
– Abra, Commented May 29, 2020 at 14:35

Adder · Accepted Answer · 2020-05-29 15:23:57Z

1

Well I investigated the question, and I found one can use json_query to select single entries from the JSON.

CREATE TABLE json_table ( JSON varchar(1024) NOT NULL , constraint CK_JSON_IS_JSON check (JSON is json));

insert into json_table columns (JSON) values ('[ { "product":"Vegetable", "name":"Potato", "price":"$60.00" }, { "product":"Fruit", "name":"Mango", "price":"$3.30" }, { "product":"Milk", "name":"Milk", "price":"$1.08" }]');

select json_query(JSON, '$[0]'), 
       json_query(JSON, '$[1]'), 
       json_query(JSON, '$[2]'), 
       json_query(JSON, '$[3]') 
from json_table;

This selects entries 0 to 3, with 3 not being found and being NULL.

You could probably stitch together a database stored procedure to return a list of the first n entries in the JSON.

answered May 29, 2020 at 15:23

Adder

5,8982 gold badges35 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pran Over a year ago

Thanks @Adder, is there any way to select subset 1-250 in one go like $[0-250}

Adder Over a year ago

I did not find a way to do that yet.

Pran Over a year ago

I found the sql but it is working fine data retun by subset is less than 4000 but if its more then it is returning null - select json_query(json, '$[0 to 250]' RETURNING clob with wrapper ) from json_table

Barbaros Özhan · Accepted Answer · 2020-05-29 16:53:02Z

An analytic function such as ROW_NUMBER() might be used within the subquery to determine the restriction, and then JSON_ARRAYAGG() and JSON_OBJECT() combination might be added to get back the reduced array :

SELECT JSON_ARRAYAGG(
                     JSON_OBJECT('product' VALUE product, 
                                 'name'    VALUE name,
                                 'price'   VALUE price) ) AS "Result"
  FROM
  (
   SELECT t.*, ROW_NUMBER() OVER (ORDER BY 1) AS rn
     FROM tab
    CROSS JOIN
     JSON_TABLE(jsdata, '$[*]' COLUMNS (
                 product VARCHAR(100) PATH '$.product',
                 name    VARCHAR(100) PATH '$.name',
                 price   VARCHAR(100) PATH '$.price'
                )
     ) t
   )
 WHERE rn <= 250

Demo

Collectives™ on Stack Overflow

oracle - fetch first n json objects from json array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related