Search Results

This can't be done. Of the two built-in GIN operators for indexing JSONB, one of them only stores hashed values, so you wouldn't be able to reverse them, and other one stores flattened keys, irrespec …

Each column name, as a JSON key, is sent in full, multiple times. … If that is the highest cost you have to pay for gratuitous use of JSON, then consider yourself lucky. …

(Changing from JSON to JSONB might speed it up slightly) …

Your JSON structure appears to be flat (based on one example), couldn't you just map those fields into real PostgreSQL columns? … Top level JSON attributes do not get the same kind of statistics on that as real columns do. …

But it also can't benefit from the gin index on the json column. …

I just wouldn't use JSON or JSONB for this purpose if I wanted speed. If you need to access a small number of keys, pulling them out into the table proper could help. …

You could keep your current UPDATE statement, but add WHERE data ? 'my-value' to it to avoid updating rows which have nothing you want to update. When v13 is released, you could use the new jsonb_set_ …

Assuming that that ship has sailed, I would at least transform it to a native PostgreSQL array, not a JSON(B) array, as that would give you better statistics estimates and so probably better execution …

Your example data and your example query do not fit together. There is no JSONB field named 'MMC' in the data, but there is in the query, for example. Assuming the query is the thing that is correct …

You can use jsonpath (to be new in version 12) to extract elements of JSON arrays based on conditionals (rather than just based on the array index), but I don't see a way to either update the extracted …

Convert the array to a text representation before storing it. It looks like you tried that, but you put the cast in the wrong place. SELECT (jsonb_build_object('location', ARRAY['world']::text))->>'l …

I don't think the archeological approach will be very useful here. There is just too much missing info and confounding variables. For example, people usually don't just add indexes for no reason. I …

You can create a function which returns the data as a string: CREATE OR REPLACE FUNCTION public.jsonb_val_to_string(jsonb) RETURNS text LANGUAGE plpgsql IMMUTABLE AS $function$ declare x text; BEG …

; But maybe you should be using some C++ library to build the JSON and pass it already formed down to PostgreSQL. … If you don't want to be dealing with JSON on the client side, you likely should not be using it on the db side either. …

level of separation, you would have to have a parent and child table, where the child table would be something like: (parent_id whatever, key text, value text, primary key (parent_id, key)) If your JSON …