Otherwise behaves according to the value of null_value_treatment which must be one of 'raise_exception', 'use_json_null', 'delete_key', or 'return_target'. The default is 'use_json_null'.

The most important part of this query is the with block. It's a powerful resource, but for this example, you can think of it as a "way to store a variable" that is the path of the contact you need to update, which will be dynamic depending on the record.

It just builds the path as '{1, value}', but we need to convert to text[] because that’s the type expected on the jsonb_path function.

For user-contributed data that's freeform and unstructured, use jsonb. It should perform as well as hstore, but it's more flexible and easier to work with.

SET object = object - 'b' || '{"a":1,"d":4}';

(The null result should not be confused with a SQL NULL; see the examples.)

And this has some immediate benefits: more efficiency, significantly faster to process, supports indexing (which can be a significant advantage, as we'll see later), simpler schema designs (replacing entity-attribute-value (EAV) tables with jsonb columns, which can be queried, indexed and joined, allowing for performance improvements up until 1000X!)

Besides efficiency, there are extra ways in which you can benefit from storing JSON in binary form. One such enhancement is the GIN (Generalized Inverted Index) indexes and a new brand of operators that come with them.

jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test') as json

jsonb_typeof(json)

FROM test x1 LEFT JOIN test x2 ON x1.id = (x2.data->>'parent')::INT;

>> We have that already, it's named 'json_each_text' > Apparently you haven't looked at json parse/deparse costs ;P Well, a PL function is gonna be none too cheap either. Using something like JSON definitely has lots to recommend it --- eg, it probably won't break when you find out your initial spec for the transport format was too simplistic.

I personaly prefer the types approach. it assures that if the function is edited, all the queries will return correct results.

To find the problematic rows you can use a query like SELECT id FROM t WHERE jsonb_typeof(data_col) <> 'object';

This means that you have a json structure different from that described in your question. You cannot use generic jsonb functions when the json structures differ in each row.

scope :with_spec_options, ->(spec_options) { where("specs->'spec_option' @> ?", spec_options.to_json) }

where("specs->'spec_option' ?| array[:options]", options: spec_options)

-- The array on the right side is not considered contained within the -- array on the left, even though a similar array is nested within it: SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- yields false -- But with a layer of nesting, it is contained: SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

Sure, with json_object_keys(). This returns a set - unlike the JavaScript function Object.keys(obj) you are referring to, which returns an array. Feed the set to an ARRAY constructor to transform it: SELECT id, ARRAY(SELECT json_object_keys(obj)) AS keys FROM tbl_items;

A more verbose form would be to spell out a LATERAL join instead of the correlated subquery: SELECT t.id, k.keys FROM tbl_items t LEFT JOIN LATERAL (SELECT ARRAY(SELECT * FROM json_object_keys(t.obj)) AS keys) k ON true;

If you have variable number of ids (or you have a lot of them), you can use json[b]_array_elements() to extract each element of the array, build up an id list and then query it with the any-containment operator ?|: select * from jsonbtest where to_json(array(select jsonb_array_elements(data) ->> 'id'))::jsonb ?| array['1884595530', '791712670'];

In most cases JSONB is likely what you want when looking for a NoSQL, schema-less, datatype

At this point it’s probably worth replacing hstore use with jsonb in all new applications.

the request to find people with matching phone numbers could be turned into a query like

If you're choosing between json/jsonb/hstore, generally choose jsonb unless you have a reason not to

The real benefit of JSONB: IndexesWe want our application to be fast. Without indexes, the database is forced to go from record to record (a table scan), checking to see if a condition is true. It’s no different with JSON data. In fact, it’s most likely worse since Postgres has to step in to each JSON document as well.

When you’re picking a data store, the most important thing to understand is where in your data — and where in its connections — the business value lies. If you don’t know yet, which is perfectly reasonable, then choose something that won’t paint you into a corner. Pushing arbitrary JSON into your database sounds flexible, but true flexibility is easily adding the features your business needs.

The BSON format used by MongoDB is limited to a maximum of 64 bits for representing an integer or floating point number, whereas the JSONB format used by Postgres does not have this limit. Postgres provides data constraint and validation functions to help ensure that JSON documents are more meaningful: for example, preventing attempts to store alphabetical characters where numerical values are expected. MongoDB offers automatic database sharding for easy horizontal scaling of JSON data storage. Scaling of Postgres installations has often been vertical. Horizontal scaling of Postgres is also possible, but tends to be more involved or use an additional third party solution. MongoDB also offers the possibility of increasing write throughput by deferring writing to disk. The tradeoff is potential loss of data, but this may suit users who have less need to persist their data.