JSON and its Query

Table of Contents

1 JSON

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate.

参考:
Introducing JSON: http://www.json.org/
The JavaScript Object Notation (JSON) Data Interchange Format: https://tools.ietf.org/html/rfc7159

1.1 格式简介


JSON格式很简单,它主要由两种结构构成:
(1) “Key/Value对”组成的集合,用大括号 {} 表示,Key和Value之间用冒号 : 分开,多个Key/Value对之间用逗号分开。
(2) 有序数组,用中括号 [] 表示,多个对象之间用逗号分开。

下面是JSON的一个例子,它表示了一个人的基本信息:

{
  "firstName": "John",
  "lastName": "Smith",
  "isAlive": true,
  "age": 25,
  "address": {
    "streetAddress": "21 2nd Street",
    "city": "New York",
    "state": "NY",
    "postalCode": "10021-3100"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    },
    {
      "type": "mobile",
      "number": "123 456-7890"
    }
  ],
  "children": [],
  "spouse": null
}

1.2 格式化JSON数据 (python -m json.tool)

有时,你得到的JSON数据可能混在一整行或者没有很好地缩进,如何格式化它,使我们可更方便地查看其内容呢?

利用Python的模块 json.tool 可以轻松地完成这个任务。如:

$ echo '{"a": "foo", "b": "bar", "c": "baz"}' | python -m 'json.tool'
{
    "a": "foo",
    "b": "bar",
    "c": "baz"
}

如果JSON数据保存在文件中, python -m json.tool file.json 可显示它格式化后的内容。如:

$ cat file.json
{"a": "foo", "b": "bar", "c": "baz"}
$ python -m json.tool file.json
{
    "a": "foo",
    "b": "bar",
    "c": "baz"
}

2 JMESPath (JSON Query)

如何在JSON数据中查找你想要的信息呢?比如,你想在节 1.1 介绍的例子中查找某人的家里电话号码(例子中为"212 555-1234"),这个任务使用sed/awk/grep等工具显得力不从心,这时,我们需要一种专门针对JSON的查询语言(如果使用后面介绍的JMESPath,则通过 phoneNumbers[?type=='home'].number | [0] 可以得到"212 555-1234")。

JMESPath is a query language for JSON. JMESPath以其作者James Saryerwinnie的名字命名。JMESPath支持Python/PHP/Javascript/Ruby/Lua/Go/Java等很多语言。

注:JMESPath并不是唯一的JSON查询语言,比如还有 JsonPath 等等,其它更多JSON查询语言可参见:http://stackoverflow.com/questions/777455/is-there-a-query-language-for-json

2.1 第一个JMESPath程序

下面以JEMESPath的Python实现jmespath(jmespath不是内置模块,需要安装)为例,介绍其简单使用。

>>> import jmespath
>>> path=jmespath.search('b', {"a": "foo", "b": "bar", "c": "baz"})
>>> print(path)
bar

参考:http://jmespath.org/tutorial.html

2.2 基本查询语法

下面是常用的JMESPath查询语法及其实例。

Table 1: JMESPath实例:Basic Expressions
查询条件 JSON数据 查询条件例子 返回结果
Identifier {"a": "foo", "b": "bar"} a "foo"
Subexpression {"a": {"b": {"c": "value"}}} a.b.c "value"
Index Expressions ["a", "b", "c", "d", "e", "f"] [1] "b"
Index Expressions ["a", "b", "c", "d", "e", "f"] [-1] "f"
Slicing [start:stop] [0, 1, 2, 3, 4, 5, 6, 7, 8] [0:3] [0, 1, 2]
Slicing [start:stop] [0, 1, 2, 3, 4, 5, 6, 7, 8] [0:-3] [0, 1, 2, 3, 4, 5]
Slicing [start:stop:step] [0, 1, 2, 3, 4, 5, 6, 7, 8] [::2] [0, 2, 4, 6, 8]
Slicing [start:stop:step] [0, 1, 2, 3, 4, 5, 6, 7, 8] [::-2] [8, 6, 4, 2, 0]
Slicing [start:stop:step] [0, 1, 2, 3, 4, 5, 6, 7, 8] [0:8:3] [0, 3, 6]

2.3 Projections

Projection allows you to apply an expression to a collection of elements. There are five kinds of projections:
(1) List Projections
(2) Slice Projections
(3) Object Projections
(4) Flatten Projections
(5) Filter Projections

2.3.1 List and Slice Projections ([*])

A wildcard expression creates a list projection, which is a projection over a JSON array.

下面是List projection的例子。假设有下面JSON数据,我们想要得到people中的所有的first名字。

{
  "people": [
    {"first": "James", "last": "d"},
    {"first": "Jacob", "last": "e"},
    {"first": "Jayden", "last": "f"},
    {"missing": "different"}
  ],
  "foo": {"bar": "baz"}
}

使用 people[*].first 可得到people中的所有的first名字,即:

people[*].first    ----> [ "James", "Jacob", "Jayden" ]

如果只想得到第2个和第3个first名字,可以这样:

people[1:3].first   ----> [ "Jacob", "Jayden" ]

2.3.2 Object Projections (星号*)

前面介绍的List projection仅可应用在JSON数组上。使用这节介绍的Object Projection可以应用在JSON对象上。

You can create an object projection using the * syntax. This will create a list of the values of the JSON object, and project the right hand side of the projection onto the list of values.

如,有下面JSON数据:

{
  "ops": {
    "functionA": {"numArgs": 2},
    "functionB": {"numArgs": 3},
    "functionC": {"variadic": true}
  }
}

应用查询条件 ops.*.numArgs 后,可以得到 [ 2, 3 ] ,即:

ops.*.numArgs      ----> [ 2, 3 ]

怎么理解它呢?把Object projection分解为LHS(left hand side)和RHS(right hand side),即:LHS为ops,RHS为numArgs。
当LHS执行后,得到下面数组:

[{"numArgs": 2}, {"numArgs": 3}, {"variadic": true}]

再应用RHS到上面数组中,得到:

[ 2, 3, null]

而null不出现在最终结果中,所以最终结果为:

[ 2, 3 ]

2.3.3 Flatten Projections (空中括号[])

Flatten Projections用 [] 表示,它有什么用呢?请看下面例子。

假设有下面JSON数据:

{
  "reservations": [
    {
      "instances": [
        {"state": "running"},
        {"state": "stopped"}
      ]
    },
    {
      "instances": [
        {"state": "terminated"},
        {"state": "runnning"}
      ]
    }
  ]
}

我们想得到一个列表里包含所有的状态,即想得到 ["running", "stopped", "terminated", "running"] ,怎么办呢?
如果使用前面介绍的List projection reservations[*].instances[*].state 作为查询会得到 [["running", "stopped"], ["terminated", "running"]] ,它不是想要的结果。
使用Flatten Projection reservations[].instances[].state 作为查询可以得到 ["running", "stopped", "terminated", "running"]

2.3.3.1 Flatten Projection一次仅操作一层数据

Flatten Projection它只会操作一层数据(不是递归操作数据)。
例如,有JOSN数据:

[
  [0, 1],
  2,
  [3],
  4,
  [5, [6, 7]]
]

做一次Flatten Projection [] 和做两次Flatten Projection [][] 的结果分别如下所示:

[]        ---->   [ 0, 1, 2, 3, 4, 5, [ 6, 7 ] ]
[][]      ---->   [ 0, 1, 2, 3, 4, 5, 6, 7 ]

2.3.4 Filter Projections

A filter expression is defined for an array and has the general form LHS [? <expression> <comparator> <expression>] RHS.

例如,想要在下面JSON数据中找到所有state为runing的machines的名字。

{
  "machines": [
    {"name": "a", "state": "running"},
    {"name": "b", "state": "stopped"},
    {"name": "b", "state": "running"}
  ]
}

使用 machines[?state=='running'].name 能实现目标,即:

machines[?state=='running'].name      ----> [ "a", "b" ]

2.4 Pipe Expressions

下面例子中,我们想得到people中第一个人的first名字(即"James"),怎么办呢?

{
  "people": [
    {"first": "James", "last": "d"},
    {"first": "Jacob", "last": "e"},
    {"first": "Jayden", "last": "f"},
    {"missing": "different"}
  ],
  "foo": {"bar": "baz"}
}

解决办法是使用Pipe Expressions,如:

people[*].first | [0]      ----> "James"
people[*].first | [1]      ----> "Jacob"

2.5 MultiSelect(创建JSON)

前面介绍的JMESPath表达式都是从JSON中找到你感兴趣的部分,而下面将要介绍的 multiselect listsmultiselect hashes 可以创建JSON元素。

下面是MultiSelect List和MultiSelect Hash的例子。

假设有JSON数据:

{
  "people": [
    {
      "name": "a",
      "state": {"name": "up"}
    },
    {
      "name": "b",
      "state": {"name": "down"}
    },
    {
      "name": "c",
      "state": {"name": "up"}
    }
  ]
}

应用MultiSelect List表达式 people[].[name, state.name] 后,可以得到:

[
  [
    "a",
    "up"
  ],
  [
    "b",
    "down"
  ],
  [
    "c",
    "up"
  ]
]

应用MultiSelect Hash表达式 people[].{myName: name, myState: state.name} 后,可以得到:

[
  {
    "myName": "a",
    "myState": "up"
  },
  {
    "myName": "b",
    "myState": "down"
  },
  {
    "myName": "c",
    "myState": "up"
  }
]

参考:http://jmespath.org/tutorial.html#multiselect

2.6 Functions

要得到下面JSON数据中people的数量,怎么办?

{
  "people": [
    {
      "name": "b",
      "age": 30,
      "state": {"name": "up"}
    },
    {
      "name": "a",
      "age": 50,
      "state": {"name": "down"}
    },
    {
      "name": "c",
      "age": 40,
      "state": {"name": "up"}
    }
  ]
}

可以使用函数length,即:

length(people)       ----> 3

参考:
http://jmespath.org/specification.html#functions
http://jmespath.org/specification.html#builtin-functions

2.6.1 Filter expression中使用函数

直接看例子,我们想找到下面JSON数据中myarray中所有包含关键字foo的元素。

{
  "myarray": [
    "foo",
    "foobar",
    "barfoo",
    "bar",
    "baz",
    "barbaz",
    "barfoobaz"
  ]
}

myarray[?contains(@, 'foo') == `true`] 可以实现上面的任务,即:

myarray[?contains(@, 'foo') == `true`]      ---->  [ "foo", "foobar", "barfoo", "barfoobaz" ]

The @ character in the example above refers to the current element being evaluated in myarray.


Author: cig01

Created: <2017-03-04 Sat 00:00>

Last updated: <2017-05-09 Tue 14:40>

Creator: Emacs 25.1.1 (Org mode 9.0.7)