JSON Column for A Single Element with Potentially Multiple Values? [message #680560] |
Thu, 21 May 2020 17:18 |
|
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 |
Solomon Yakobson
Messages: 3286 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.
|
|
|