The issue when I don't use AGG type I get a 'ora-01422: exact fetch returns more than requested number of rows using a json_object put into clob so using JSON_ARRAYAGG works I just haven't been able to figure out how to format the query properly to return the JSON object that I need.
I'm not a Oracle guru, or database admin, so that's probably the issue, any help from you guys would be great. In the meantime I will continue to play with it and maybe I can find my own solution.
This is my Stored Procedure Package Body:
PROCEDURE getCities
(
v_province_fips IN geo_counties.FIPS_CODE%TYPE,
v_city_like in geo_cities.NAME%TYPE,
p_out OUT CLOB
)
AS
v_country_code geo_cities.country_code%TYPE;
v_fips_code geo_counties.FIPS_CODE%TYPE;
BEGIN
v_country_code := SUBSTR('' || v_province_fips || '',1,2);
SELECT JSON_ARRAYAGG(
JSON_OBJECTAGG('CITY_SEARCH' VALUE
JSON_OBJECT(
'GEONAME_ID_CITY' VALUE c.GEONAME_ID,
'NAME' VALUE c.NAME,
'ASCII_NAME' VALUE c.ASCII_NAME,
'LATITUDE' VALUE c.LATITUDE,
'LONGITUDE' VALUE c.LONGITUDE,
'STATE_PROV_NAME' VALUE a.NAME,
'GEONAME_ID_COUNTY' VALUE b.GEONAME_ID,
'COUNTY_NAME' VALUE b.NAME,
'COUNTY_ASCII_NAME' VALUE b.ASCII_NAME,
'STATE_PROV' VALUE p.NAME,
'COUNTRY_CODE' VALUE c.COUNTRY_CODE,
'COUNTY_LATITUDE' VALUE b.LATITUDE,
'COUNTY_LONGITUDE' VALUE b.LONGITUDE,
'FIPS_CODE' VALUE b.FIPS_CODE
)
)
RETURNING CLOB)
INTO p_out
FROM geo_cities c
JOIN geo_counties b
ON (b.COUNTRY_CODE = c.COUNTRY_CODE)
JOIN GEO_ADMIN1_CODES_ASCII a
ON (a.FIPS_CODE = v_country_code || '.' || SUBSTR('' || b.FIPS_CODE || '',4,2))
JOIN GEO_PROVINCES p
ON (p.FIPS_CODE = v_country_code || '.' || SUBSTR('' || b.FIPS_CODE || '',4,2))
where LOWER( c.NAME ) like LOWER( '%' || v_city_like || '%' )
AND c.COUNTRY_CODE = v_country_code
AND c.ADMIN_1 = SUBSTR('' || b.FIPS_CODE || '',4,2)
AND c.ADMIN_2 = SUBSTR('' || b.FIPS_CODE || '',7)
AND c.feature_code in ('ADM1','ADM2','PPL','PPLA', 'PPLA2')
GROUP BY
c.GEONAME_ID,
c.NAME,
c.ASCII_NAME,
c.LATITUDE,
c.LONGITUDE,
c.COUNTRY_CODE,
c.ADMIN_1,
b.ADMIN_2,
b.GEONAME_ID,
b.NAME,
a.NAME,
b.ASCII_NAME,
c.COUNTRY_CODE,
b.LATITUDE,
b.LONGITUDE,
b.POPULATION,
b.TIME_ZONE,
b.FIPS_CODE
ORDER BY c."NAME", b.NAME,c.ADMIN_1;
END getCities;
This returns the following json object:
[
{
"CITY_SEARCH": {
"GEONAME_ID_CITY": 4164138,
"NAME": "Miami",
"ASCII_NAME": "Miami",
"LATITUDE": 25.77427,
"LONGITUDE": -80.19366,
"STATE_PROV_NAME": "Florida",
"GEONAME_ID_COUNTY": 4164238,
"COUNTY_NAME": "Miami-Dade County",
"COUNTY_ASCII_NAME": "Miami-Dade County",
"STATE_PROV": "Florida",
"COUNTRY_CODE": "US",
"COUNTY_LATITUDE": 25.60897,
"COUNTY_LONGITUDE": -80.49867,
"FIPS_CODE": "US.FL.086"
}
},
{
"CITY_SEARCH": {
"GEONAME_ID_CITY": 4164143,
"NAME": "Miami Beach",
"ASCII_NAME": "Miami Beach",
"LATITUDE": 25.79065,
"LONGITUDE": -80.13005,
"STATE_PROV_NAME": "Florida",
"GEONAME_ID_COUNTY": 4164238,
"COUNTY_NAME": "Miami-Dade County",
"COUNTY_ASCII_NAME": "Miami-Dade County",
"STATE_PROV": "Florida",
"COUNTRY_CODE": "US",
"COUNTY_LATITUDE": 25.60897,
"COUNTY_LONGITUDE": -80.49867,
"FIPS_CODE": "US.FL.086"
}
}
]
The query works as expected, it's the JSON format that is the issue. I want to return a JSON object as follows:
{
"CITY_SEARCH": [
{
"GEONAME_ID_CITY": 4164143,
"NAME": "Miami Beach",
"ASCII_NAME": "Miami Beach",
"LATITUDE": 25.79065,
"LONGITUDE": -80.13005,
"STATE_PROV_NAME": "Florida",
"GEONAME_ID_COUNTY": 4164238,
"COUNTY_NAME": "Miami-Dade County",
"COUNTY_ASCII_NAME": "Miami-Dade County",
"STATE_PROV": "Florida",
"COUNTRY_CODE": "US",
"COUNTY_LATITUDE": 25.60897,
"COUNTY_LONGITUDE": -80.49867,
"FIPS_CODE": "US.FL.086"
},
{
"GEONAME_ID_CITY": 4164138,
"NAME": "Miami",
"ASCII_NAME": "Miami",
"LATITUDE": 25.77427,
"LONGITUDE": -80.19366,
"STATE_PROV_NAME": "Florida",
"GEONAME_ID_COUNTY": 4164238,
"COUNTY_NAME": "Miami-Dade County",
"COUNTY_ASCII_NAME": "Miami-Dade County",
"STATE_PROV": "Florida",
"COUNTRY_CODE": "US",
"COUNTY_LATITUDE": 25.60897,
"COUNTY_LONGITUDE": -80.49867,
"FIPS_CODE": "US.FL.086"
}
]
}
You want to get rid of the JSON_OBJECTAGG and the GROUP BY clause:
PROCEDURE getCities (
v_province_fips IN geo_counties.FIPS_CODE%TYPE,
v_city_like IN geo_cities.NAME%TYPE,
p_out OUT CLOB
)
AS
v_country_code geo_cities.country_code%TYPE := SUBSTR(v_province_fips,1,2);
BEGIN
SELECT JSON_OBJECT(
KEY 'CITY_SEARCH' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'GEONAME_ID_CITY' VALUE c.GEONAME_ID,
KEY 'NAME' VALUE c.NAME,
KEY 'ASCII_NAME' VALUE c.ASCII_NAME,
KEY 'LATITUDE' VALUE c.LATITUDE,
KEY 'LONGITUDE' VALUE c.LONGITUDE,
KEY 'STATE_PROV_NAME' VALUE a.NAME,
KEY 'GEONAME_ID_COUNTY' VALUE b.GEONAME_ID,
KEY 'COUNTY_NAME' VALUE b.NAME,
KEY 'COUNTY_ASCII_NAME' VALUE b.ASCII_NAME,
KEY 'STATE_PROV' VALUE p.NAME,
KEY 'COUNTRY_CODE' VALUE c.COUNTRY_CODE,
KEY 'COUNTY_LATITUDE' VALUE b.LATITUDE,
KEY 'COUNTY_LONGITUDE' VALUE b.LONGITUDE,
KEY 'FIPS_CODE' VALUE b.FIPS_CODE
)
RETURNING CLOB
)
RETURNING CLOB
)
INTO p_out
FROM geo_cities c
JOIN geo_counties b
ON (b.COUNTRY_CODE = c.COUNTRY_CODE)
JOIN GEO_ADMIN1_CODES_ASCII a
ON (a.FIPS_CODE = v_country_code || '.' || SUBSTR(b.FIPS_CODE,4,2))
JOIN GEO_PROVINCES p
ON (p.FIPS_CODE = v_country_code || '.' || SUBSTR(b.FIPS_CODE,4,2))
where LOWER( c.NAME ) like LOWER( '%' || v_city_like || '%' )
AND c.COUNTRY_CODE = v_country_code
AND c.ADMIN_1 = SUBSTR(b.FIPS_CODE,4,2)
AND c.ADMIN_2 = SUBSTR(b.FIPS_CODE,7)
AND c.feature_code in ('ADM1','ADM2','PPL','PPLA', 'PPLA2');
END getCities;
Also, '' || 'something' is pointless as '' is identical to NULL and NULL || 'something' is simply 'something'.