Home » SQL & PL/SQL » SQL & PL/SQL » JSON Column for A Single Element with Potentially Multiple Values? (Oracle 19c)
JSON Column for A Single Element with Potentially Multiple Values? [message #680560] Thu, 21 May 2020 17:18 Go to next message
bmccollum
Messages: 15
Registered: April 2020
Junior Member
I'm trying to get my head around how to setup an Oracle column in a table that will contain JSON-formatted data.

I understand how to (and have successfully set this up several times) setup a JSON-enabled column that contains multiple elements, let's say...
Column Name: "ContactInfo"
Elements: Name, Company, Address1, Address2, City, State, Zip

(Example)
{"Name" : "Neil Peart","Company" : "Rush","Address1" : "1122 Drumline Avenue","Address2" : "Apartment # 2","City" : "Memphis","State" : "TN","Zip" : "38002"}

My new situation though is that I've been asked to convert a "PartNumber" column to a JSON-enabled column that could, going forward, contain multiple part numbers in that column.

So, the revised "PartNumber" JSON-enabled/structured column could just contain a single part number (example: "A100")...

Or, that column could contain multiple part numbers (example: "A100","B100","C100","D100").

Can anyone clue me in on what the format of that column should be for this scenario?

Many thanks in advance!
Re: JSON Column for A Single Element with Potentially Multiple Values? [message #680561 is a reply to message #680560] Thu, 21 May 2020 17:33 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
WITH SAMPLE_DATA AS (
                     SELECT  1 ID,'["A100"]' PART_NUMBER_JSON FROM DUAL UNION ALL
                     SELECT  2,'["A100","B100","C100","D100"]' FROM DUAL
                    )
SELECT  ID,
        PART_NUMBER
  FROM  SAMPLE_DATA,
        JSON_TABLE(
                   PART_NUMBER_JSON,
                   '$[*]'
                   COLUMNS(
                           PART_NUMBER VARCHAR2(10) PATH '$'
                          )
                  )
/

        ID PART_NUMBER
---------- -----------
         1 A100
         2 A100
         2 B100
         2 C100
         2 D100

SQL>
SY.
Previous Topic: The cost of FILTER operations
Next Topic: Using associated array as SP parameter
Goto Forum:
  


Current Time: Thu Mar 28 04:21:08 CDT 2024