代码之家 › 专栏 › 技术社区 › David

Oracle 19c如何编写具有JSON输出的存储过程,我在格式化查询以返回特定JSON对象时遇到了问题

json-arrayagg oracle19c oracle json

0

David · 技术社区 · 1 年前

当我不使用AGG类型时,我得到的问题是'ora-01422:使用放入clob中的json_objects,精确获取返回的行数超过了请求的行数,因此使用json_ARRAYAGG是可行的,我只是不知道如何正确格式化查询以返回我需要的json对象。

我不是Oracle专家,也不是数据库管理员,所以这可能是问题所在,你们的任何帮助都会很棒。与此同时,我会继续玩它,也许我可以找到自己的解决方案。

这是我的存储过程包正文:

  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;

这将返回以下json对象:

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

查询按预期工作,问题在于JSON格式。我想返回一个JSON对象,如下所示:

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

2 回复 | 直到 1 年前

1

2

MT0 1 年前

你想摆脱 JSON_OBJECTAGG 以及 GROUP BY 条款:

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;

也, '' || 'something' 毫无意义,因为 '' 与 NULL 和 NULL || 'something' 很简单 'something' .

2

Alex Poole 1 年前

你不需要 objectagg ,或分组。

通过一个简化的演示,您的查询是这样做的:

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(“城市搜索”值JSON_OBJECT(“名字_城市”值c.GEONAME_ID,“名字”值c.NAME)))
[{“城市搜索”:{“地名ID_CITY”:4164138,“名称”:“迈阿密”}},{“城镇搜索”:{'地名ID_CITY”:4164143,“名称:“迈阿密海滩”}]

或

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

…这是你所看到的内容的简化版本。

如果你有一个值为 json_arrayagg 对于对象,则不需要(或不希望)分组方式,您将得到:

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

jsonobject('CITY_SEARCH'值JSON_ARRAYAGG(jsonobject,'GEONAME_ID_CITY'VALUEC.GEONAME_ID,'NAME'VALUEC.NAME))
{“城市搜索”:[{“地名ID_CITY”:4164138,“名称”:“迈阿密”},{“地名ID CITY”:4164143,“名称“:迈阿密海滩”}}

或

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

…这是你想看到的缩小版。

fiddle