0

Let I have a JSONb data:

{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      },
      ...
    ]
  }
}

I have the JSONPath expression $.track.segments ? (@.location[1] < 13.4) that finds the desired objects in an array (returns the 2nd object in the example array $.track.segments).

Then I need to modify the found object in the original JSONb data.
As answered here How to modify field with jsonPath in PostgreSQL? I need to use jsonb_set function.

How do I get the path of the found object that is suitable for passing to the jsonb_set function? (In this example, the value is '{track,segments,2}')
Or, at least, the index of the found object in the segments array?

Alexander Tolmachev
  • 153
  • 1
  • 5
  • 2
    Your problems are due to the wrong data structure. Segments should be stored in a regular table. Read more: [PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns.](https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/) – klin Jan 20 '21 at 06:21
  • @klin The question here is not about the correctness of the data structure. Moreover, this is not my structure, but is taken from the official PostgreSQL documentation: https://www.postgresql.org/docs/13/functions-json.html#FUNCTIONS-SQLJSON-PATH – Alexander Tolmachev Jan 21 '21 at 11:38
  • That's only advice (a good one I think). You can take it or not, your choice. Note, that the example in the docs is not a column of a table but *some JSON data from a GPS tracker that you would like to parse.* Please read the documentation carefully. – klin Jan 21 '21 at 13:02
  • @klin Please, note, that the sample data in the my question is not a column of a table too. There is no one word about data storing. It is only about manipulating data using PostgreSQL functions. – Alexander Tolmachev Jan 28 '21 at 17:00
  • Postgres is a database system designed for efficient data storage and retrieval, not a tool for manipulating external data. – klin Jan 28 '21 at 18:12

0 Answers0