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

Answer 1

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