Menu
The json_array type of doctrine is saved to a postgres database > 9.2 as json datatype.
Postgres supports contains and lots of other operations on the json datatype. Is there any possibility to use the functionalities in doctrine?
Probably a custom SQLWalker is needed for that? As described here. Also a custom type supporting JSONB would be nice. As described here. This would increase the performance when querying for the json field. Or is there a custom lib that adds the json(b) functionality or is it even possibile using DQL.
410
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
JSONB and IndexesPostgreSQL can use indexes for the text results as compare operands. GIN index can be used by the GIN JSONB operator class.
JSONB provides a wide array of options to index your JSON data. At a high-level, we are going to dig into 3 different types of indexes – GIN, BTREE and HASH. Not all index types support all operator classes, so planning is needed to design your indexes based on the type of operators and queries that you plan on using.
What's jsonb. The data types json and jsonb , as defined by the PostgreSQL documentation,are almost identical; the key difference is that json data is stored as an exact copy of the JSON input text, whereas jsonb stores data in a decomposed binary form; that is, not as an ASCII/UTF-8 string, but as binary code.
I created a Symfony2 Bundle that supports jsonb.
Hope this helps.
https://github.com/boldtrn/JsonbBundle
Right now I prefer to use NativQueries instead of custom DQL functions to query for my jsonb fields. The custom functions are too cumbersome in my application.
Everything should be documented in the Bundle as well.
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
