SQL to convert JSON object into array of objects in Posgres

Question

I have a column of JSON type in a postgres table. It currently has values like this

{"value": "abc"}

I want to write a SQL query that can change this to

[{"value": "abc", "timestamp": 1465373673}]

The part timestamp: 1465373673 will be hard coded

Any ideas on how this SQL query can be written?

Jeremy · Accepted Answer · 2020-01-07 13:39:12Z

2

You can use json_build_array and json_build_object:

UPDATE test 
set a = json_build_array(
          json_build_object('value', a->'value', 'timestamp', 1465373673)
        );

Here's a fiddle.

answered Jan 7, 2020 at 13:39

Jeremy

6,77322 silver badges21 bronze badges

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

Comments

klin · Accepted Answer · 2020-01-07 13:40:05Z

2

Use the concatenation operator and the function jsonb_build_array():

select jsonb_build_array('{"value": "abc"}'::jsonb || '{"timestamp": 1465373673}');

              jsonb_build_array
---------------------------------------------
 [{"value": "abc", "timestamp": 1465373673}]
(1 row)

Read JSON Functions and Operators.

answered Jan 7, 2020 at 13:40

klin

123k15 gold badges240 silver badges262 bronze badges

Collectives™ on Stack Overflow

SQL to convert JSON object into array of objects in Posgres

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