![]() If the output column is of type json or jsonb, the JSON value is just reproduced exactly. P.S.: Improved using cstork's suggestion. To convert a JSON value to the SQL type of an output column, the following rules are applied in sequence: A JSON null value is converted to a SQL null in all cases. The paths returned are in #> operator or jsonb_set function format, but that's easy to change. RETURN NEXT array_append(prefix, counter::text) ![]() RETURN QUERY SELECT * FROM jsonb_paths (value, array_append(prefix, counter::text)) RETURN QUERY SELECT * FROM jsonb_paths (value, array_append(prefix, key)) The major practical difference is one of efficiency. The json and jsonb data types accept almost identical sets of values as input. To implement efficient query mechanisms for these data types, PostgreSQL also provides the jsonpath data type described in Section 8.14.7. IF jsonb_typeof(value) IN ('array', 'object') THEN PostgreSQL offers two types for storing JSON data: json and jsonb. In the same context, I'd like to find out if you can suggest better ways to accomplish the objective.ĬREATE OR REPLACE FUNCTION jsonb_paths (data jsonb, prefix text) RETURNS SETOF text LANGUAGE plpgsql AS $$ My intention is to write a function that will be called recursively, which looks for the $ entries and replaces them with the values from the other table, then returns the final JSON. Let's focus on "ref4" from my sample query. The choice of $ is unfortunate, because it is also a part of JSONPath syntax, but this is a legacy system, so things have to stay this way. However, what I need is a list of paths to these matches, i.e., "$",Ĭontext: a database contains jsonb values, where the $ sign is a placeholder for a value stored in a different table. Here is a minimal working example that illustrates my point, the query finds all the values of keys named "$" at any level in the given JSON: select jsonb_path_query(
0 Comments
Leave a Reply. |