U0600008: jsonb_extract_path does not support XXX as a parameter

Description

Database Type and Version

• Source database type and version: all PostgreSQL versions
• Target database type and version: all GaussDB versions

Syntax Example

The jsonb_query_path function of PostgreSQL allows you to use JSONPath expressions to obtain specific information from JSONB data. If there are only two parameters, UGO converts the jsonb_query_path function into the jsonb_extract_path function of GaussDB. If there are more than two parameters, UGO does not convert them.

The jsonb_extract_path function of GaussDB obtains specific information from JSONB data based on the specified path. However, the jsonb_extract_path function needs to specify a specific path and does not support condition-based filtering or wildcard characters (*). This error message is reported when the JSONPath expression of the jsonb_query_path function contains a path that is not supported by jsonb_extract_path. For example:

SELECT jsonb_path_query(data, '$.address.*') FROM users;
SELECT jsonb_path_query(data, '$.hobbies[*]') FROM users;
SELECT jsonb_path_query(data, '$.hobbies[?(@ == "reading")]') FROM users;

Suggestion

You can use either of the following methods:

1. Modify the JSONPath expression, delete the paths that are not supported by GaussDB, such as the asterisk (*) and condition-based filtering, and use a clear JSONB path to obtain information.

2. Delete unsupported paths, return more data to the application that sends the SQL statement, and modify the application code to filter data.