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