Querying nested hash of hashes using PostgreSQL

user_2818661 0
Viewed 250

I have a json datatype field to store complex data. JSON data looks like this hash:

{
  "0" => {
    "origin" => {},
    "diff" => {
      "type" => "type_1",
      ...
    }
  },
  "1" => {
    "origin" => {
      "type" => "type_2",
      ...
    },
    "diff" => {
      ...
    }
  },
  ...
}

I've tried to transform json to array to avoid these index keys, but it did not help me.

WITH data_values AS (
  SELECT id, array_to_json(array(SELECT t.v from json_each_text(data) as t(k,v))) as array_data
  FROM event_logs
)
SELECT * FROM data_values
WHERE array_data->'origin'->>'type' = 'type_3' OR array_data->'diff'->>'type' = 'type_3'

Also, I had an idea to use json_object_keys and iterate over top-level keys to find the necessary key/value pair, but I'm a newbie to PSQL and I have some problems with solving that problem.

A version of PSQL is 11, so the JSON path is not available for me.

Table definition example:

CREATE TABLE event_logs (
    id integer,
    data json,
    created_at timestamp without time zone
);

INSERT INTO event_logs (id, data)
VALUES 
  (1, '{"0": {"origin": {}, "diff": {"type": "type_1"}}, "1": {"origin": {"type": "type_1"}, "diff": {}}}'),
  (2, '{"0": {"origin": {}, "diff": {"type": "type_2"}}, "1": {"origin": {}, "diff": {"type": "type_3"}}}'),
  (3, '{"0": {"origin": {}, "diff": {"type": "type_3"}}, "1": {"origin": {"type": "type_2"}, "diff": {}}}')

Important note: there can be a different count of top-level keys.

I want to find records by the key/value pair (for example, type = 'type_3'). It should select records with ID 2 and 3.

Can you help me to do it right?

jsonb postgresql json
1 Answers

Storing the objects as an array instead of an integer-indexed object is a good idea, but that doesn't let you skip this level with the -> operators. (Only jsonpath can do that).

You will need to use the json_each iteration inside your WHERE clauses:

SELECT * FROM data_values
WHERE EXISTS(
  SELECT *
  FROM json_each(data)
  WHERE value->'origin'->>'type' = 'type_3'
     OR value->'diff'->>'type' = 'type_3'
);

(If you had used an array, json_each would become json_array_elements).

user_10485720
Related