Convert array of string to array of Json objects in Postgresql Jsonb

Question

i have a jsonb column like

{
    "foo":{"bar" :["b","c","d"]}
}

Which I want to convert all items of "bar" array to a json with a "name" key result shall be:

{
    "foo":{"bar" :[{"name":"b"},{"name":"c"},{"name":"d"}]}
}

I wish to do it using postgresql queries in all rows of my table.

I tried various queries and functions but my main issue is at wrapping part of my strings.

Tips and helps are appriciated

["name":"b","name":"c","name":"d"] is invalid because it is not an object notation. Also same keys in the object is not allowed — Joven28
– Joven28, Commented Sep 23, 2019 at 3:59
Are you actually using Postgres 9.1? It looks like native JSON support wasn't added until 9.2 and JSONB wasn't added until 9.4. — ravioli
– ravioli, Commented Sep 23, 2019 at 5:41

ravioli · Accepted Answer · 2019-09-23 10:39:26Z

3

Assuming you're using a version of Postgres with JSON support (9.4+), this should get you close:

WITH cte(myJSON) AS (
  SELECT CAST('{"foo":{"bar" :["b","c","d"]}}'AS JSONB) AS MyJSON 

  UNION ALL

  SELECT CAST('{"foo":{"bar" :["e","f","g"]}}'AS JSONB) AS MyJSON  
) 

SELECT
  JSON_AGG(
    (SELECT ROW_TO_JSON(_) FROM (SELECT name) AS _)
  ) myrow
FROM (
  SELECT JSONB_ARRAY_ELEMENTS(myjson->'foo'->'bar') AS name,
    ROW_NUMBER() OVER() AS RowNum
  FROM cte
) src
GROUP BY src.RowNum

This will return [{"name":"b"},{"name":"c"},{"name":"d"}].

You can then construct your final JSON as needed.

SQL Fiddle
Reference

Update
This is very hacky and I'm sure there's a cleaner way, but I updated the query above to handle multiple rows. Just replace the CTE references with your actual table name and myjson with the name of your JSON column.

Output:

[{"name":"b"},{"name":"c"},{"name":"d"}]
[{"name":"e"},{"name":"f"},{"name":"g"}]

Let me know if that works.

Updated Update
Here's an UPDATE statement you can use:

UPDATE t1 tgt
SET jsoncol = JSONB_SET(
  jsoncol, -- Source JSON
  '{foo,bar}', -- Target node to update
  src.new_json -- New value 
)
FROM (
  SELECT 
    ID, 
    JSONB_AGG(
      (SELECT TO_JSONB(_) FROM (SELECT name) AS _) -- Convert new row to JSON
    ) new_json
  FROM (
    SELECT 
      ID, 
      JSONB_ARRAY_ELEMENTS(jsoncol->'foo'->'bar') AS name -- Convert array to rows
    FROM t1
  ) src
  GROUP BY src.ID
) src
WHERE tgt.ID = src.ID -- Update "tgt" table with rows from "src" table
;

DB-Fiddle

edited Sep 23, 2019 at 10:39

answered Sep 23, 2019 at 6:18

ravioli

3,8213 gold badges17 silver badges35 bronze badges

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

6 Comments

Nil Null Over a year ago

It works only for a single row, any idea how can I loop over all rows ?

Nil Null Over a year ago

It works charm, but now I face another issue, I am updating this table which only bar supposed to change , any clue on how to write the update ?

ravioli Over a year ago

You can use something like this: UPDATE MyTable SET jsoncol = JSONB_SET(jsoncol, '{foo,bar}', <new_JSON_value>). This will replace the value in foo->bar with new_JSON_value. If you want to use the query above you'll need to do an UPDATE FROM and join the "src" table to the "tgt" table. What is the PK of the table you want to update?

Nil Null Over a year ago

It has an ID as primary key. I tried your approach but it is copying all rows

Nil Null Over a year ago

what do you mean by "tgt" table ?

|

Collectives™ on Stack Overflow

Convert array of string to array of Json objects in Postgresql Jsonb

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related