Postgresql – postgres jsonb return specific paths of json object

jsonpostgresql

Consider the following jsonb object:

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

I would like to query the object, to return only the following jsonb object, given the folloiwng paths:

track.segments.hr

{
  "track": {
    "segments": [
      {
        "HR": 73
      },
      {
        "HR": 135
      }
    ]
  }
}

How can i formulate the query, to retrieve this kind of data from the json, and still return the json object, with only the specified paths?

Best Answer

You almost certainly can't.

JSON doesn't [yet] have the equivalent of XML's XSLTransform, which could do what you describe, but [still] only after retrieving the entire blob of XML to work with.

Databases are really good at finding little bits of stuff and putting them together; they're far less good and taking large blobs of stuff and pulling them apart.

I assume what you're showing is a simplification but what you've shown could be done easily with ordinary, relational tables.

Related Solutions

PostgreSQL JSON – Storing Large Nested Objects

As with most things, it depends.

First of all, if you're not 100% certain that the data will always be valid JSON, use a normal text type instead.

If the data is indeed always JSON, I would use a JSON type if for no other reason that as documentation that the column contains JSON data.

If there will ever be any chance of requiring indexing of data, go for the JSONB datatype. If insert performance is critical, choose JSON (or even a text type) instead. The JSON datatype stores the JSON data as-is, while the JSONB datatype removes empty nodes and whitespace and even duplicate keys (the last one is kept). This means that JSONB is storing less data, but requires minor reconstruction on retrieval.

For all other cases, I would go with JSONB as the "best" generic JSON type in PostgreSQL at present, and this is also the recommendation in the documentation. See http://www.postgresql.org/docs/9.4/static/datatype-json.html

Postgresql – Postgres function to merge two json objects with overlapping keys into one object

If you're using PostgreSQL 9.5 (at least), you can use the concatenation operator (||) on jsonb types (and you can convert json to jsonb if necessary first).

For example:

WITH test(data) AS (
    VALUES ('{
        "a" : {
            "0" : 2,
            "1" : 4,
            "3" : 6
        },
        "b" : {
            "2" : 8,
            "1" : 10,
            "4" : 12
        }
    }'::jsonb)
)
SELECT (data->'a') || (data->'b') FROM test

will produce:

{"0": 2, "1": 10, "2": 8, "3": 6, "4": 12}

(Please note that, in this particular example, the parentheses around (data->'a') matter.)

You can modify the example above to get a specific value, as you requested, for example:

SELECT (((data->'a') || (data->'b'))->'1')::text::integer FROM test

Best Answer

Related Solutions

PostgreSQL JSON – Storing Large Nested Objects

Postgresql – Postgres function to merge two json objects with overlapping keys into one object

Related Question