Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Oracle 19c how to write a stored procedure with JSON output, I'm having trouble formatting the query to return a specific JSON object

David
September 2, 2024
58 views
0 votes
2 Answers

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"
        }
    ]
}

Answers

You don’t need the objectagg, or the grouping.

With a simplified demo your query is doing this:

SELECT JSON_ARRAYAGG(
        JSON_OBJECTAGG('CITY_SEARCH' VALUE
            JSON_OBJECT(
                'GEONAME_ID_CITY' VALUE c.GEONAME_ID,
                'NAME' VALUE c.NAME
            )
        )
    )
FROM c
GROUP BY c.GEONAME_ID, c.NAME

JSON_ARRAYAGG(JSON_OBJECTAGG(‘CITY_SEARCH’VALUEJSON_OBJECT(‘GEONAME_ID_CITY’VALUEC.GEONAME_ID,’NAME’VALUEC.NAME)))
[{"CITY_SEARCH":{"GEONAME_ID_CITY":4164138,"NAME":"Miami"}},{"CITY_SEARCH":{"GEONAME_ID_CITY":4164143,"NAME":"Miami Beach"}}]

[
  {
    "CITY_SEARCH": {
      "GEONAME_ID_CITY": 4164138,
      "NAME": "Miami"
    }
  },
  {
    "CITY_SEARCH": {
      "GEONAME_ID_CITY": 4164143,
      "NAME": "Miami Beach"
    }
  }
]

… which is a cut-down version of what you are seeing.

If you instead have a single object with a value that is an json_arrayagg of objects, then you don’t need (or want) the group-by, and you get:

SELECT JSON_OBJECT('CITY_SEARCH' VALUE
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'GEONAME_ID_CITY' VALUE c.GEONAME_ID,
                'NAME' VALUE c.NAME
            )
        )
    )
FROM c

JSON_OBJECT(‘CITY_SEARCH’VALUEJSON_ARRAYAGG(JSON_OBJECT(‘GEONAME_ID_CITY’VALUEC.GEONAME_ID,’NAME’VALUEC.NAME)))
{"CITY_SEARCH":[{"GEONAME_ID_CITY":4164138,"NAME":"Miami"},{"GEONAME_ID_CITY":4164143,"NAME":"Miami Beach"}]}

{
  "CITY_SEARCH": [
    {
      "GEONAME_ID_CITY": 4164138,
      "NAME": "Miami"
    },
    {
      "GEONAME_ID_CITY": 4164143,
      "NAME": "Miami Beach"
    }
  ]
}

… which is a cut-down version of what you want to see.

fiddle

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'.

Please signup or login to give your own answer.

Click here to cancel reply.