Querying a json type string Postgres-CodePudding

I have a text column in my db with a json structure. Postgres v.10

It could look like this:

{"pe_cd":"07","me_cd":"006","mf":"4.0","mfm":"4.0","pot":null,"earliestDate":[{"date":"2019-07-01","_destroy":false},{"date":"2020-01-01","_destroy":""}],"earliestDate2":null,"leavingDate":null}

If earliestDate2 = null then i need to make a update query and fill this with the first date from the earliestDate[]. In this case 2019-07-01

Goal in this case should be:

{"pe_cd":"07","me_cd":"006","mf":"4.0","mfm":"4.0","pot":null,"earliestDate":[{"date":"2019-07-01","_destroy":false},{"date":"2020-01-01","_destroy":""}],"earliestDate2":"2019-07-01","leavingDate":null}

How can i write this update query?

Fiddle

CodePudding user response：

to query your json type string, you can do this:

`SELECT * FROM yourtable
WHERE json_like_column::JSON->'earliestDate' IS NULL

:: will cast your string into JSON and -> will get 'eraliestDate' field from your json. you can also use ->> to convert the value of that field to string.

CodePudding user response：

Here is what I came up with for you:

WITH null_dates AS
( -- Find the records with a NULL earliestDate2 and expand the dates stored
    SELECT id, (jsonb_array_elements(attr->'earliestDate')->>'date')::DATE as d
      FROM test
     WHERE attr->>'earliestDate2' IS NULL
)
UPDATE test t
   SET attr = attr||jsonb_build_object('earliestDate2', sub.edate)
  FROM
     ( -- Get the min date per ID from CTE (null_dates)
         SELECT id
              , MIN(d) AS edate
           FROM null_dates
          GROUP BY id
     ) sub
 WHERE t.id = sub.id
;

Here is a fiddle to show it in action.