JSON Column for A Single Element with Potentially Multiple Values? [message #680560] |
Thu, 21 May 2020 17:18 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/bfe65e238b8fc6f87f5889f51e5b3b79?s=64&d=mm&r=g) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
Solomon Yakobson
Messages: 3273 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.
|
|
|