Home » SQL & PL/SQL » SQL & PL/SQL » ear multivalue json as rows (oracle 19)
icon5.gif  ear multivalue json as rows [message #681459] Mon, 20 July 2020 05:33 Go to next message
wjuteau
Messages: 2
Registered: July 2020
Junior Member
Hello,

I need help on how to read a JSON multi values entry as several rows.
I hope a soul can help me there Razz


I figured it out with JSON_TABLE for unique value (see below), but fails with multiple
simple value data :
{"keys":["25094"]}
my query :
JSON_TABLE(textvalue, '$'
COLUMNS
  (stringvalue VARCHAR2(100) PATH '$.keys')) AS jt
PS: I escapted the [ and ] earlier in the code


Simple example for my challenge:
my table is composed of 2 columns : one string "mystring" + last one "json" like
{"keys":["25094","36316","36317","40836","45223"]}
I'd like to have a result like this with my select :
row1: mystring | 25094
row2: mystring | 36316
row3: mystring | 36317
row4: mystring | 40836
row5: mystring | 45223

Thanks a lot if you can help
Re: ear multivalue json as rows [message #681460 is a reply to message #681459] Mon, 20 July 2020 05:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2987
Registered: January 2010
Location: Connecticut, USA
Senior Member
with sample as (
                select  'mystring' col1,
                        '{"keys":["25094","36316","36317","40836","45223"]}' col2
                  from  dual
               )
select  col1,
        key
  from  sample,
        json_table(
                   col2,
                   '$.keys[*]'
                   columns
                     key number path '$'
                  )
/

COL1            KEY
-------- ----------
mystring      25094
mystring      36316
mystring      36317
mystring      40836
mystring      45223

SQL>
SY.
Re: ear multivalue json as rows [message #681470 is a reply to message #681460] Tue, 21 July 2020 01:54 Go to previous message
wjuteau
Messages: 2
Registered: July 2020
Junior Member
Thanks a ton ! Surprised Razz
Previous Topic: Sorting of columns in alternative order.
Next Topic: XML over 64K
Goto Forum:
  


Current Time: Mon Sep 28 14:59:59 CDT 2020