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?

Improve this question
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. Commented Jan 20, 2021 at 6: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: postgresql.org/docs/13/… Commented Jan 21, 2021 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. Commented Jan 21, 2021 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. Commented Jan 28, 2021 at 17:00
  • Postgres is a database system designed for efficient data storage and retrieval, not a tool for manipulating external data. Commented Jan 28, 2021 at 18:12
Related questions
2642
How to show tables in PostgreSQL?
1994
How can I change a PostgreSQL user password?
1976
How to exit from PostgreSQL command line utility: psql
Load 6 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.