![]() P.S.: Improved using cstork's suggestion. The paths returned are in #> operator or jsonb_set function format, but that's easy to change. RETURN NEXT array_append(prefix, counter::text) Real life example: List last 10 tweets (unindexed). ![]() ![]() To extract that description, I would use a scalar sub-query: select (select jsonbbuildobject ('description', t.descr -> 'content') from jsonbarrayelements (h.hoteldata -> 'description') as t (descr) where t.descr. We make medical / financial software using Python and Postgres. PostgreSQL provides the JSON array data type that allows us to store and query the JSON data quickly and efficiently. 1 Im trying to do a very simple query in Postgres 13. RETURN QUERY SELECT * FROM jsonb_paths (value, array_append(prefix, counter::text)) You will have to expand the array if you want to do that and apply the condition in the WHERE clause - see GMB's answer. RETURN QUERY SELECT * FROM jsonb_paths (value, array_append(prefix, key)) 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. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |