JSON_TABLE processing
Posted by:
DK Singh
Date: October 03, 2024 09:54PM
SET @json_data = '{"PhCity":["Houston","Brooklyn","New york","Memphis","Hytxsville"]}';
SET @phcity_list = (
SELECT GROUP_CONCAT(CONCAT("'", value, "'"))
FROM JSON_TABLE(@json_data, '$.PhCity[*]' COLUMNS (value TEXT PATH '$')) AS jt
);
select @phcity_list;
this is my json and want to get @phcity_list output in 'Houston','Brooklyn','Memphis' so that I can use it in my query like 'select * from MyTable where City in(@phcity_list ) limit 100'. But currently select @phcity_list; returns me result like '\'HOUSTON\',\'FAR ROCKAWAY\',\'WAUSAU\',\'KINGSTREE\',\'ORANGE CITY\'' so, due to '\' no result(s) able to get. Looking a genuine solution for the same. Can't use table variable because it gives error that table variable can't be reopen. There are several uses of this json in a single stored procedure
Subject
Views
Written By
Posted
JSON_TABLE processing
212
October 03, 2024 09:54PM
Sorry, only registered users may post in this forum.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.