Just a slight variation to Chris's answer:
SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM json_to_record('{"a": 1, "b": ["hello", "There"], "c": "bar"}')
AS x(a int, b text, d text);
The idea is the same: massage the JSON array into an array - in this case, through an array literal. In addition to a bit cleaner looking code (though I love it, regex usually does not help much in this regard :), it seems slighly faster, too:
CREATE TABLE jsonb_test (
id serial,
data jsonb
);
INSERT INTO jsonb_test (id, data)
SELECT i, format('{"a": %s, "b": ["foo", "bar"], "c": "baz"}', i::text)::jsonb
FROM generate_series(1,10000) t(i);
SELECT a, string_to_array(regexp_replace(b, '\[*\"*\s*\]*','','g'),',') AS b, d
FROM jsonb_test AS j,
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);
-- versus
SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM jsonb_test AS j,
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);
On this dataset and on my test box, the regex version shows and average execution time of 300 ms, while my version shows 210 ms.
You can use the @> operator.
From the docs:
@> Does the left JSON value contain within it the right value?
So in your case it would look something like this:
select * from products where specs @> '{"spec_options": ["a", "b", "c"]}';
Best Answer
It appears that you have an array of JSON values, and want to turn that into a single JSONB value which is an array.
You can unnest your array, then cast the elements to JSONB and aggregate that back using
jsonb_agg()
: