2

I have data and a set of rules in the database. I'd like to check which rules does this data satisfy. My data looks like this in JSON notation:

{
  "items": [{"id": 1, "amount": 10}, {"id": 2, "amount": 20}, {id: 3, "amount": 30}],
  "location": "A",
  "segment": "X"
}

I have 3 rules:

  1. The list of items must contain an item with an id of 1 AND the amount must be less than 5.
  2. The location must NOT be "A" OR the segment must be "X".
  3. The items array includes an item with an id of 1 OR an item with an id of 2.

In this example, the satisfied rules are 2 and 3. How should I store these rules in the database such that it's efficient to retrieve the rules that are satisfied by my data?

JsonLogic allows to express logical statements in JSON. An idea is to write my rules in JsonLogic then store in database. I can check them using a library that uses JsonLogic. I'm unsure about the efficiency of this approach, especially when the number of rules grows.

1 Answer 1

2

You can use jsonpath queries, using the jsonb_path_exists function. See the documentation.

CREATE TABLE YourTable(id int, yourJson jsonb);
CREATE TABLE Rules(id int, rule jsonpath);

INSERT INTO YourTable VALUES (1, '{
  "items": [{"id": 1, "amount": 10}, {"id": 2, "amount": 20}, {"id": 3, "amount": 30}],
  "location": "A",
  "segment": "X"
}');

INSERT INTO Rules VALUES
  (1, '$ ? ( exists( @.items[*] ? ( @.id == 1 && @.amount < 5) ) )'),
  (2, '$ ? ( @.location != "A" || @.segment == "X" )'),
  (3, '$ ? ( exists( @.items[*] ? ( @.id == 1 || @.id == 3 ) ) )'),
  (4, '$ ? ( exists( @.items[*] ? ( @.id == 4 || @.id == 5 ) ) )')
SELECT
  t.yourJson,
  r.id,
  r.rule,
  jsonb_path_exists(t.yourJson, r.rule)
FROM YourTable t
CROSS JOIN Rules r

db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.