postgresql : ERROR: path element at position 1 is not an integer:

Question

I am trying to update a field value in jsonb column with text value present in another column but getting some syntax errors; not getting any solution.

i am trying to swap values of OutgoingVertexid & IncomingVertexId in below JSONB

'[
      {
         "Edgeid":10,
         "Weight":100,
         "Active":1,
         "OutgoingVertexid":"",
         "IncomingVertexid":"G",
         "EdgeColor":"Black"
      }
   ]
'

so used below code by putting all values OutgoingVertexid & IncomingVertexid value in temp table.

UPDATE temp_table 
        SET 
        owner_property = CASE 
                            WHEN owner_outgoing_edge IS NOT NULL 
                                THEN jsonb_set(owner_property, '{OutgoingVertexid}', '""')
                            ELSE 
                                jsonb_set(owner_property, '{OutgoingVertexid}', ''||owner_incoming_edge::jsonb||'') 
                                END;

but getting below error:

ERROR: path element at position 1 is not an integer:

Thanks in Advance

user1822 · Accepted Answer · 2020-07-09 06:43:18Z

1

Your json value is an array, not a single value. So you need to pick the array element you want to change by including the index in the array you pass to jsonb_set()

UPDATE temp_table 
  SET owner_property = CASE 
                        WHEN owner_outgoing_edge IS NOT NULL 
                          THEN jsonb_set(owner_property, '{0, OutgoingVertexid}', '""')
                        ELSE 
                          jsonb_set(owner_property, '{0, OutgoingVertexid}', to_jsonb(owner_incoming_edge)) 
                       END

answered Jul 9, 2020 at 6:43

user1822

@a_horse_with_no_name...Thanks for your response. As i am beginner to JSON so could not get this solution and to avoid time i just used a below hack ... UPDATE temp_table SET owner_property = replace(owner_property::text,'OutgoingVertexid','123')::jsonb; UPDATE temp_table SET owner_property = replace(owner_property::text,'IncomingVertexid','OutgoingVertexid')::jsonb; UPDATE temp_table SET owner_property = replace(owner_property::text,'123','IncomingVertexid')::jsonb;

VBAGuy
– VBAGuy

2020-07-09 07:30:45 +00:00
Commented Jul 9, 2020 at 7:30
@a_horse_with_no_name...if i go with your solution sometimes i am getting "" in my temp_table column while extracting value. How can i check in that case as WHEN owner_outgoing_edge IS NOT NULL is not getting passed.

VBAGuy
– VBAGuy

2020-07-09 17:13:39 +00:00
Commented Jul 9, 2020 at 17:13

Add a comment |

Stack Exchange Network

postgresql : ERROR: path element at position 1 is not an integer:

1 Answer 1

Your Answer

Hot Network Questions

postgresql : **ERROR: path element at position 1 is not an integer:**

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions

postgresql : ERROR: path element at position 1 is not an integer: