Build jsonb array from jsonb field

Question

I have column options with type jsonb , in format {"names": ["name1", "name2"]} which was created with

UPDATE table1 t1 SET options = (SELECT jsonb_build_object('names', names) FROM table2 t2 WHERE t2.id= t1.id)

and where names have type jsonb array.

SELECT jsonb_typeof(names) FROM table2 give array

Now I want to extract value of names as jsonb array. But query

SELECT jsonb_build_array(options->>'names') FROM table 

gave me ["[\"name1\", \"name2\"]"], while I expect ["name1", "name2"]

How can I get value in right format?

Answer 1

The ->> operator will return the value of the field (in your case, a JSON array) as a properly escaped text. What you are looking for is the -> operator instead.

However, note that using the jsonb_build_array on that will return an array containing your original array, which is probably not what you want either; simply using options->'names' should get you what you want.

Answer 2

Actually, you don't need to use jsonb_build_array() function.

Use select options -> 'names' from table; This will fix your issue.

jsonb_build_array() is for generating the array from jsonb object. You are following wrong way. That's why you are getting string like this ["[\"name1\", \"name2\"]"].

Try to execute this sample SQL script:

select j -> 'names' from(select '{"names": ["name1", "name2"]}' :: JSONB as j) as a;