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?

Dologan · Accepted Answer · 2019-09-09 16:16:24Z

4

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.

answered Sep 9, 2019 at 16:16

Dologan

4,8342 gold badges34 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sebastián Palma · Accepted Answer · 2021-09-14 22:22:01Z

3

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;

edited Sep 14, 2021 at 22:22

Sebastián Palma

33.6k6 gold badges45 silver badges65 bronze badges

answered Sep 9, 2019 at 16:21

Mayur

5,1294 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

Build jsonb array from jsonb field

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related