How to update a specific JSON element inside a complex JSON document in postgresql 10

Question

I have JSON document which is stored under single column of type jsonb inside postgresql which looks like below:

{
  "resourceType": "Bundle",
  "type": "transaction",
  "entry": [
    {
      "fullUrl": "urn:uuid:100",
      "resource": {
        "resourceType": "Encounter",
        "id": "110",
        "status": "planned",
        "priority": {
          "coding": [
            {
              "code": "ASAP"
            }
          ]
        },
        "subject": {
          "reference": "Patient/123"
        },
        "appointment": [
          {
            "reference": "Appointment/12213#42"
          }
        ],
        "diagnosis": [
          {
            "condition": {
              "reference": "Condition/condReferenceValue"
            },
            "use": {
              "coding": [
                {
                  "system": "http://terminology.hl7.org/CodeSystem/diagnosis-role",
                  "code": "AD"
                },
                {
                  "system": "http://terminology.hl7.org/CodeSystem/diagnosis-role",
                  "code": "DD"
                }
              ]
            }
          }
        ],
        "hospitalization": {
          "preAdmissionIdentifier": {
            "system": "https://system.html"
          }
        },
        "location": [
          {
            "location": {
              "display": "Mumbai"
            },
            "status": "active"
          },
          {
            "status": "planned"
          }
        ]
      },
      "request": {
        "method": "POST",
        "url": "Encounter"
      }
    }
  ]
}

Now, I want to update value for reference under subject attribute. So, I tried below way but it throws an error:

update fhir.testing set names = jsonb_set(names,'{"subject":{"reference"','"Patient/1"',true) where id = 10;

Error:

SQL Error [22P02]: ERROR: malformed array literal: "{"subject":{"reference""
  Detail: Unexpected array element.

I referred this link but didn't work out for me. How can I do it?

Please, if you are asking about **postgresql** don't tag as **mysql** — nacho, Jul 21 '20 at 14:40
Similar to this: https://stackoverflow.com/questions/63009837 — , Jul 21 '20 at 14:55
is the json document stored under the name `names` and has type jsonb? — , Jul 21 '20 at 15:04

score 2 · Answer 1 · 2020-07-22T08:31:02.423

I don't use Postgres that much but from what i read in the relative jsonb_set example in the documentation of JSON functions (and since you want to update) shouldn't it be

jsonb_set(names, '{entry,0,subject,reference}','Patient/1', false)

instead of

jsonb_set(names,'{"subject":{"reference"','"Patient/1"',true)

jsonb

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and the item designated by path does not exist. As with the path oriented operators, negative integers that appear in path count from the end of JSON arrays.

EDIT

To explain the path used in jsonb_set, check this example.

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)

returns

[{"f1":[2,3,4],"f2":null},2,null,3]

As i understand if a sub-element in a complex JSON document is an array, you need to specify it's index e.g. 0,1,2,...

EDIT

Always look very carefully the structure of the JSON document. I simply write this because i did not see that subject was a child of resource and that is causing you the error.

So the correct path is actually '{entry,0,resource,subject,reference}'

When I tried to use your way it throw an below error `ERROR: invalid input syntax for type json LINE 1: ... = jsonb_set(names, '{entry,0,subject,reference}','Patient/1... ^ DETAIL: Token "Patient" is invalid.` — whatsinthename, Jul 22 '20 at 06:53
You are correct, i am sorry for this mistake. I have not seen that subject was a child of resource. So the query fails because the path is incorrect. Thanks for the feedback. — , Jul 22 '20 at 08:27

score 1 · Accepted Answer · answered Jul 21 '20 at 16:49

1

Correct Query for your requirement is:

update fhir.testing 
set names= jsonb_set(names, '{entry,0,resource,subject,reference}', '"Patient/1"' , false) 
where id = 10;

Explanation

json_set takes 4 parameter

target_json (jsonb) - which accept jsonb type data. In your case it is names field.
path (text[]) - which accepts a text array. in your case it is '{entry,0,resource,subject,reference}'.
new_value (jsonb) - in your case you want to change it to '"Patient/1"'.
create_missing (boolean) - in your case it should be false. as you want to replace the existing one. if you want to create the reference with given value in case of not found then just mark it true.

answered Jul 21 '20 at 16:49

Akhilesh Mishra

5,876
3
16
32

Yeah this worked for me @Akhilesh. Also, I was little bit curious for understanding that is there way that I could make it generic. Like, by creating a function which will take the input parameter, search in the JSON doc and update the value by passing some second parameter to that function. Just need some hint – whatsinthename Jul 22 '20 at 06:56
Also, what does `0` indicates in that text array. I am new to PostgreSQL so, trying to understand things – whatsinthename Jul 22 '20 at 06:58
1. Yes it is possible a little bit effort is required. 2. As you can see that `entry` is an array, so `0` denotes first object of `entry` array and `resource,subject,reference` denotes the path in it – Akhilesh Mishra Jul 22 '20 at 08:39
You can try [this](https://stackoverflow.com/questions/49022189/retrieving-json-elements-with-a-specific-key-name-from-a-complex-nested-structur) – Akhilesh Mishra Jul 22 '20 at 08:49

score 0 · Answer 3 · answered Jul 21 '20 at 14:39

0

the value is not valid json, try this out:

update fhir.testing set names = jsonb_set(names, '{"entry": [{"resource": {"subject":{"reference":"Patient/1"} }}]}',true) where id = 10;

You have to create a valid json, closing every { and every ], yours was

'{"subject":{"reference"'

answered Jul 21 '20 at 14:39

dariogriffo

4,148
3
17
34

When I try your query it says that `jsonb_set` doesn't exist – whatsinthename Jul 22 '20 at 06:54

How to update a specific JSON element inside a complex JSON document in postgresql 10

3 Answers3