onelittlenightmusic/0-opa-json-cheatsheet.md

## 0-opa-json-cheatsheet.md

      
    Raw
  

              0-opa-json-cheatsheet.md
            
          
    jr, jp, jb (alias to opa eval): Cheatsheet

jr, jp and jb are commands for JSON modification.
Basic usages

$ data='{"name":"chris", "friends":["alice", "bob"]}'
# jr
$ echo $data | jr 'i.name'
"chris"

# jr + jp
$ echo $data | jr 'i.friends' | jp i
[
  "alice",
  "bob"
]

# jb
$ echo $data | jb 'n = i.name; f = i.friends'
{
  "f": [
    "alice",
    "bob"
  ],
  "n": "chris"
}

# jb + jr
$ echo $data | jb 'n = i.name; f = i.friends' | jr 'sprintf("%s->%s", [i.n, i.f])'
chris->["alice", "bob"]
Installation

1. Install opa CLI

MacOS

brew install opa
Others

https://www.openpolicyagent.org/docs/latest/#running-opa
2. Configure jr alias

bash, zsh

Add the following lines to ~/.zshrc or ~/.bashrc
alias jo="opa eval --import 'input as i' -d ~/.oparc --import 'data.f'"
# The above line works after v0.27.1. Before v0.27.0, the next works
# alias jo=”opa eval -I --import ‘input as i’” --package p
alias jpo="jo --format=pretty"
alias jro="jo --format=raw"
alias jbo="jo --format=bindings"
alias jb="jbo -I"
alias jp="jpo -I"
alias jr="jro -I"
alias jbi="jb -i"
alias jpi="jp -i"
alias jri="jr -i"
Create ~/.oparc with this command.
echo 'package f' > ~/.oparc 

Command variation


Command
Description


jr (r comes from raw)
single value evaluation with standard input


└ jri
with file input


└ jro
without input


jb (b comes from bindings)
multiple values evaluation with standard input


└ jbi
with file input


└ jbo
without input


jp (p comes from pretty)
show JSON in prettified manner with standard input


└ jpi
with file input


└ jpo
without input


Query

The query syntax is originated in OPA.


Syntax
Description
Example


i
Get input
i


.<field>
Select a field
i.name


[]
Field selection in object. 
Element selection in array.  
Element selection in set
i[0], i["name"]


{}
Set construction
{"alice", "bob"}


[]
Array construction
["alice", "bob"]


$ data='{"field":"hello world"}'
$ echo $data | jr 'i'
{"field":"hello world"}

$ jro '{"field":"hello world"}'
{"field":"hello world"}

$ echo $data | jr 'i.field'
hello world

$ data='{"field":["hello world", "bye"]}'
$ echo $data | jr 'i.field'
["hello world","bye"]
Validate JSON

$ data='{"field":"hello world"}'
$ echo $data | jr 'i.field'
hello world

$ echo $data | jr 'i.field2'

$ data='[{"field":"hello world", "field2": "test"}, {"field3":"hello world", "field4": "test"}]'
$ echo $data | jr '{a|a=i[_]; a.field; a.field2}'
[{"field":"hello world","field2":"test"}]

$ data='[{"name": "array1", "array": [1,2]}, {"name": "array2", "array": [1,2,3,4]}]'
# Look for a record that has an element 3 in its array.
$ echo $data | jr '{a.name|a=i[_]; a.array[_] == 3}'
["array2"]

# Look for a record that has an element 3 or 5 or 7 in its array.
$ echo $data | jr '{a.name|a=i[_]; a.array[_] == {3,5,7}[_]}'
["array2"]

# Look for a record that doesn't have an element 3 in its array.
$ echo $data | jr '{a.name|a=i[_]; count({t|a.array[t] == 3})==0}'
["array1"]

# Look for a record that doesn't have an element 3 in its array.
$ echo $data | jr '{a.name|a=i[_]; count(a.array)>3}'
["array2"]
Create an object


Goal
Example query


Create a new object
a = {"test": "dummy"}


Create a new object from another object
b = {"test2": a}


$ jro '{"field":"hello world"}'
{"field":"hello world"}

$ data='{"field":"hello world"}'
$ echo $data | jr 'i'
{"field":"hello world"}

$ echo $data | jr '{"newobj": i}'
{"newobj":{"field":"hello world"}}
Dealing with an object


Goal
Example query


Get a field
a.test or a["test"]


Get all keys
{k|a[k]}


Get all values
{v|v:=a[_]}


Create a new object from some values
a = {k:v|k:="test"; v:="dummy"}


$ data='{"field":"hello world", "anotherfield": "anothervalue"}'
$ echo $data | jr '{k|i[k]}'
["field","anotherfield"]
$ echo $data | jr '{v|v:=i[_]}'
["anothervalue","hello world"]
$ echo $data | jr '{k:v| v:=i[key]; k:=concat("", [key, "2"])}'
{"anotherfield2":"anothervalue","field2":"hello world"}
Dealing with an array


Goal
Example query


Get an element from array
a[0]


Get a slice of array
array.slice(a, 1, 3)


Generate an array of numbers
numbers.range(0, 2)


Get count of array
count([1])


$ data='{"field":["new world", "bye"]}'
$ echo $data | jr 'i.field[0]'
"new world"

$ echo '5' | jri 'numbers.range(0, i)'
[0,1,2,3,4,5]
Dealing with a set or an array


Goal
Example query


Get maximum from array
max([1,2,3])


Get minimum from array
min([1,2,3])


Searching for records, group by


Goal
Example query


Search for a record with key 
(like select * from a where a.name == "mine")
{r|r = a[_]; r.name == "mine"}


Search for a record with max
{r|r = a[_]; r.weight == max({x|x = a[_].weight})}


Get sum of records
sum({x|x = a[_].weight})}


$ data='[{"name": "alice", "weight":1},{"name": "bob", "weight":2}]'
$ echo $data | jr '{r|r = i[_]; r.name == "bob"}'
[{"name":"bob","weight":2}]
$ echo $data | jr '{r|r = i[_]; r.weight > 0}'
[{"name":"alice","weight":1},{"name":"bob","weight":2}]
$ echo $data | jr '{r|r = i[_]; r.weight == max({x|x = i[_].weight})}'
[{"name":"bob","weight":2}]
$ echo $data | jr 'sum({x|x = i[_].weight})'
3
Join multiple tables


Goal
Example query


Join two tables
{{"r": r, "r2": r2}|r = a[_]; r2 = b[_]; r.name == r2.name }


Search for a record with max
{r|r = a[_]; r.weight == max({x|x = a[_].weight})}


Get sum of records
sum({x|x = a[_].weight})}


Raw data
$ data='[{"name": "alice", "weight":1, "friends": ["bob"]},{"name": "bob", "weight":2, "friends": ["alice", "chris"]},{"name": "chris", "weight":4, "friends": ["bob"]}]'
$ echo $data | jp i
[
  {
    "friends": [
      "bob"
    ],
    "name": "alice",
    "weight": 1
  },
  {
    "friends": [
      "alice",
      "chris"
    ],
    "name": "bob",
    "weight": 2
  },
  {
    "friends": [
      "bob"
    ],
    "name": "chris",
    "weight": 4
  }
]
Self join
$ echo $data | jr '{{"friends_weight_sum": {"one": a.name, "two": b.name, "weight_sum": s}} |
  a = i[_]
  b = i[_]
  a.name == b.friends[_]
  s = a.weight+b.weight
}' | jp i
[
  {
    "friends_weight_sum": {
      "one": "alice",
      "two": "bob",
      "weight_sum": 3
    }
  },
  {
    "friends_weight_sum": {
      "one": "bob",
      "two": "alice",
      "weight_sum": 3
    }
  },
  {
    "friends_weight_sum": {
      "one": "bob",
      "two": "chris",
      "weight_sum": 6
    }
  },
  {
    "friends_weight_sum": {
      "one": "chris",
      "two": "bob",
      "weight_sum": 6
    }
  }
]
Self join (2): Find a pair of friends (smaller/larger weight and diff)
$ echo $data | jr '{{"friends_weight_compare": {"smaller": a.name, "larger": b.name, "diff": d}} |
  a = i[_]
  b = i[_]
  a.name == b.friends[_]
  a.weight < b.weight
  d = b.weight - a.weight
}' | jp i
[
  {
    "friends_weight_compare": {
      "diff": 1,
      "larger": "bob",
      "smaller": "alice"
    }
  },
  {
    "friends_weight_compare": {
      "diff": 2,
      "larger": "chris",
      "smaller": "bob"
    }
  }
]
Pattern match


Goal
Example query
Reference


Check if a string begins with some substring
startswith(x, "abc")
ref


Check if a string ends with some substring
endswith(x, "abc")
ref


Check if a string contains some substring
contains(x, "abc")
ref


Check if a string begins with some substring
regex.match(".*pattern.*", x)
ref


Initialize data
$ data='[{"name": "alice", "weight":1, "friends": ["bob"]},{"name": "bob", "weight":2, "friends": ["alice", "chris", "dian"]},{"name": "chris", "weight":4, "friends": ["bob", "elic"]}]'
Search for those who has friends "bo*"
$ echo $data | jr '{ a.name |
  a = i[_]
  startswith(a.friends[_], "bo")
}'
["alice","chris"]
Search for those who has friends matching with ".*ice".
$ echo $data | jr '{ a.name |
  a = i[_]
  regex.match(".*ice", a.friends[_])
}'
["bob"]
Search for those who has friends matching with ".i." but no friends matching with ".*x".
$ echo $data | jr '{ a.name|
  a = i[_]
  regex.match(".*i.*", a.friends[_])
  false == regex.match(".*x", a.friends[_])
}'
["bob", "chris"]
Search for substring like "..ic" in friends' names
$ echo $data | jr '{ any_any_ic |
  a = i[_].friends[_]
  any_any_ic = regex.find_n("..ic", a, -1)[_] # find_n returns array. [_] flattens all results
}'
["alic","elic"]
Recursive search


Goal
Example query
Reference


Make graph object to search
graph.reachable
ref


Initialize data
$ data='[{"name": "alice", "children": ["elic"]},{"name": "bob", "children": ["alice"]},{"name": "chris", "children": ["elic"]}, {"name": "dian", "children": []}, {"name": "elic", "children": []}]'
Search for descendants
$ echo $data | jr '{ name: reachable |
  graph = { node.name: edges |
    node = i[_]
    edges = node.children # or object.get(node, "children", [])
  }
  name = i[_].name
  reachable = graph.reachable(graph, {name})
}' | jp '{"descendants": i }'
{
  "descendants": {
    "alice": [
      "alice",
      "elic"
    ],
    "bob": [
      "bob",
      "alice",
      "elic"
    ],
    "chris": [
      "chris",
      "elic"
    ],
    "dian": [
      "dian"
    ],
    "elic": [
      "elic"
    ]
  }
}

$ echo $data | jr 'graph = { node.name: edges |
    node = i[_]
    edges = node.children # or object.get(node, "children", [])
  }
  name = i[_].name
  reachable = graph.reachable(graph, {name})
' | jp '{"descendants": i.reachable }'
Rules

Define rule

Make a file filter.rego that contains some rules (e.g. format1 and format2 are rules)
package f

import input as i

format1 = {r |
  a := i[x].name
  b := i[x].friends
  c := concat(", ", b)
  r := sprintf("(name=%s, friends=%s)", [a,c])
}

format2 = {r |
  a := i[x].name
  b := i[x].friends
  c := concat("+", b)
  r := sprintf("\<name: %s, friends: %s\>", [a,c])
}
Call jr with -d file option.
$ data='[{"name": "alice", "friends": ["bob"]},{"name": "bob", "friends": ["alice", "chris"]},{"name": "chris", "friends": ["bob"]}]'

$ echo $data | jr -d filter.rego 'data.f.format1'
["(name=alice, friends=bob)","(name=bob, friends=alice, chris)","(name=chris, friends=bob)"]

$ echo $data | jr -d filter.rego 'data.f.format2'
["alice knows bob","bob knows alice+chris","chris knows bob"]
--import option can make alias.
$ echo $data | jr -d filter.rego --import 'data.f' 'f.format1'
["(name=alice, friends=bob)","(name=bob, friends=alice, chris)","(name=chris, friends=bob)"]
Make function

package f

import input as i

format(input_data) = {r |
  a := input_data[x].name
  b := input_data[x].friends
  c := concat(", ", b)
  r := sprintf("(name=%s, friends=%s)", [a,c])
}
$ data='[{"name": "alice", "friends": ["bob"]},{"name": "bob", "friends": ["alice", "chris"]},{"name": "chris", "friends": ["bob"]}]'

$ echo $data | jr -d filter.rego --import 'data.f' 'f.format(i)'
["(name=alice, friends=bob)","(name=bob, friends=alice, chris)","(name=chris, friends=bob)"]
Examples

e1. Solve Traveling Salesman Problem

$ data='[]'

$ echo $data | jr '{{"length": l, "order": o} | 
  i[t0]
  i[t1]
  i[t2]
  i[t3]
  i[t4]
  numbers.range(0, 4) == sort({t0, t1, t2, t3, t4})
  o = [t0, t1, t2, t3, t4]
  pArray = [p | p = i[o]]
  m = numbers.range(0, 3)[_]
  dx = (p[m].x - p[m+1].x)
  dy = (p[m].y - p[m+1].y)
  lxy = dx * dx + dy * dy
}'

  
## dot.oparc
package f
import input as i

exist(array) = true {
  is_array_or_set(array)
  count(array)>0
}

not_exist(array) = true {
  is_array_or_set(array)
  not exist(array)
}

contain(array, elem) = true {
  is_array_or_set(array)
  array[_] == elem
}

not_contain(array, elem) = true {
  is_array_or_set(array)
  not contain(array, elem)
}

is_array_or_set(array) = true {
  is_array(array)
}

is_array_or_set(array) = true {
  is_set(array)
}

contain_all(array, array2) = true {
  set := make_set(array)
  set2 := make_set(array2)
  set2 == set & set2
}

contain_any(array, array2) = true {
  set := make_set(array)
  set2 := make_set(array2)
  exist(set & set2)
  # or set[_] == set2[_]
}

make_set(array) = {x| x:= array[_]}
Command	Description
`jr` (`r` comes from `raw`)	single value evaluation with standard input
└ `jri`	with file input
└ `jro`	without input
`jb` (`b` comes from `bindings`)	multiple values evaluation with standard input
└ `jbi`	with file input
└ `jbo`	without input
`jp` (`p` comes from `pretty`)	show JSON in prettified manner with standard input
└ `jpi`	with file input
└ `jpo`	without input
Syntax	Description	Example
`i`	Get input	`i`
`.<field>`	Select a field	`i.name`
`[]`	Field selection in object. Element selection in array. Element selection in set	`i[0]`, `i["name"]`
`{}`	Set construction	`{"alice", "bob"}`
`[]`	Array construction	`["alice", "bob"]`
Goal	Example query
Create a new object	`a = {"test": "dummy"}`
Create a new object from another object	`b = {"test2": a}`
Goal	Example query
Get a field	`a.test` or `a["test"]`
Get all keys	`{k\|a[k]}`
Get all values	`{v\|v:=a[_]}`
Create a new object from some values	`a = {k:v\|k:="test"; v:="dummy"}`
Goal	Example query
Get an element from array	`a[0]`
Get a slice of array	`array.slice(a, 1, 3)`
Generate an array of numbers	`numbers.range(0, 2)`
Get count of array	`count([1])`
Goal	Example query
Get maximum from array	`max([1,2,3])`
Get minimum from array	`min([1,2,3])`
Goal	Example query
Search for a record with key (like `select * from a where a.name == "mine"`)	`{r\|r = a[_]; r.name == "mine"}`
Search for a record with max	`{r\|r = a[_]; r.weight == max({x\|x = a[_].weight})}`
Get sum of records	`sum({x\|x = a[_].weight})}`
Goal	Example query
Join two tables	`{{"r": r, "r2": r2}\|r = a[_]; r2 = b[_]; r.name == r2.name }`
Search for a record with max	`{r\|r = a[_]; r.weight == max({x\|x = a[_].weight})}`
Get sum of records	`sum({x\|x = a[_].weight})}`
Goal	Example query	Reference
Check if a string begins with some substring	`startswith(x, "abc")`	ref
Check if a string ends with some substring	`endswith(x, "abc")`	ref
Check if a string contains some substring	`contains(x, "abc")`	ref
Check if a string begins with some substring	`regex.match(".pattern.", x)`	ref
	package f
	import input as i

	exist(array) = true {
	is_array_or_set(array)
	count(array)>0
	}

	not_exist(array) = true {
	is_array_or_set(array)
	not exist(array)
	}

	contain(array, elem) = true {
	is_array_or_set(array)
	array[_] == elem
	}

	not_contain(array, elem) = true {
	is_array_or_set(array)
	not contain(array, elem)
	}

	is_array_or_set(array) = true {
	is_array(array)
	}

	is_array_or_set(array) = true {
	is_set(array)
	}

	contain_all(array, array2) = true {
	set := make_set(array)
	set2 := make_set(array2)
	set2 == set & set2
	}

	contain_any(array, array2) = true {
	set := make_set(array)
	set2 := make_set(array2)
	exist(set & set2)
	# or set[_] == set2[_]
	}

	make_set(array) = {x\| x:= array[_]}