1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
|
.. _map-json-to-spreadsheet:
Mapping JSON to spreadsheet
===========================
This tutorial covers how to map JSON document to a spreadsheet document, very
similar to what we covered in :ref:`this tutorial <mapping-xml-to-ss>`
where we illustrated how to map XML document to a spreadsheet document.
Throughout this tutorial, we will be using :download:`this sample JSON document <example.json>`
to illustrate how to achieve it using the ``orcus-json`` command. The structure
of this tutorial will be similar to the structure of the XML mapping counterpart,
since the steps are very similar.
Examining the structure of the input JSON document
--------------------------------------------------
Let's first take a look at the sample JSON document:
.. code-block:: javascript
[
{
"id": 1,
"name": [
"Tab",
"Limpenny"
],
"active": true,
"gender": "Male",
"language": "Kazakh"
},
{
"id": 2,
"name": [
"Manda",
"Hadgraft"
],
"active": false,
"gender": "Female",
"language": "Bislama"
},
{
"id": 3,
"name": [
"Mickie",
"Boreham"
],
"active": false,
"gender": "Male",
"language": "Swahili"
},
...
This is essentially the same content as the XML sample document we used in the
:ref:`last tutorial <mapping-xml-to-ss>` but re-formatted in JSON.
Let run the following command:
.. code-block::
orcus-json --mode structure example.json
to analyze the structure of this JSON document. The command will generate the
following output:
.. code-block::
$array[20].object(*)['active'].value
$array[20].object(*)['gender'].value
$array[20].object(*)['id'].value
$array[20].object(*)['language'].value
$array[20].object(*)['name'].array[2].value[0,1]
This structure output resembles a variant of JSONPath but some modifications
are applied. It has the following characteristics:
* The ``$`` symbol represents the root of the structure.
* Array node takes the form of either ``array[N]``, where the value of ``N``
represents the number of elements.
* Object node takes the form of ``object['key']``.
* Value node, which is always a leaf node, is represented by ``value`` except
when the leaf node is an array containing values, it takes the form of ``value[0,1,2,...]``.
* The ``.`` symbols represent the node boundaries.
* The ``(*)`` symbols represent recurring nodes, which can be either array or
object.
Auto-mapping the JSON document
------------------------------
Let's map this JSON document to a spreadsheet document by running:
.. code-block::
orcus-json --mode map -o out -f flat example.json
This is very similar to what we did in the XML mapping tutorial, except that
the command used is ``orcus-json`` and the input file is ``example.json``.
This will create file named ``out/range-0.txt`` which contains the following:
.. code-block::
---
Sheet name: range-0
rows: 21 cols: 6
+--------+-----------+-------------+-----------+--------+----------------+
| id | field 0 | field 1 | active | gender | language |
+--------+-----------+-------------+-----------+--------+----------------+
| 1 [v] | Tab | Limpenny | true [b] | Male | Kazakh |
+--------+-----------+-------------+-----------+--------+----------------+
| 2 [v] | Manda | Hadgraft | false [b] | Female | Bislama |
+--------+-----------+-------------+-----------+--------+----------------+
| 3 [v] | Mickie | Boreham | false [b] | Male | Swahili |
+--------+-----------+-------------+-----------+--------+----------------+
| 4 [v] | Celinka | Brookfield | false [b] | Female | Gagauz |
+--------+-----------+-------------+-----------+--------+----------------+
| 5 [v] | Muffin | Bleas | false [b] | Female | Hiri Motu |
+--------+-----------+-------------+-----------+--------+----------------+
| 6 [v] | Jackelyn | Crumb | false [b] | Female | Northern Sotho |
+--------+-----------+-------------+-----------+--------+----------------+
| 7 [v] | Tessie | Hollingsbee | true [b] | Female | Fijian |
+--------+-----------+-------------+-----------+--------+----------------+
| 8 [v] | Yank | Wernham | false [b] | Male | Tok Pisin |
+--------+-----------+-------------+-----------+--------+----------------+
| 9 [v] | Brendan | Lello | true [b] | Male | Fijian |
+--------+-----------+-------------+-----------+--------+----------------+
| 10 [v] | Arabel | Rigg | false [b] | Female | Kyrgyz |
+--------+-----------+-------------+-----------+--------+----------------+
| 11 [v] | Carolann | McElory | false [b] | Female | Pashto |
+--------+-----------+-------------+-----------+--------+----------------+
| 12 [v] | Gasparo | Flack | false [b] | Male | Telugu |
+--------+-----------+-------------+-----------+--------+----------------+
| 13 [v] | Eolanda | Polendine | false [b] | Female | Kashmiri |
+--------+-----------+-------------+-----------+--------+----------------+
| 14 [v] | Brock | McCaw | false [b] | Male | Tsonga |
+--------+-----------+-------------+-----------+--------+----------------+
| 15 [v] | Wenda | Espinas | false [b] | Female | Bulgarian |
+--------+-----------+-------------+-----------+--------+----------------+
| 16 [v] | Zachary | Banane | true [b] | Male | Persian |
+--------+-----------+-------------+-----------+--------+----------------+
| 17 [v] | Sallyanne | Mengue | false [b] | Female | Latvian |
+--------+-----------+-------------+-----------+--------+----------------+
| 18 [v] | Elizabet | Hoofe | true [b] | Female | Tswana |
+--------+-----------+-------------+-----------+--------+----------------+
| 19 [v] | Alastair | Hutchence | true [b] | Male | Ndebele |
+--------+-----------+-------------+-----------+--------+----------------+
| 20 [v] | Minor | Worland | true [b] | Male | Dutch |
+--------+-----------+-------------+-----------+--------+----------------+
Again, this is very similar to what we saw in the XML-mapping example. Note
that cell values with ``[v]`` and ``[b]`` indicate numeric and boolean values,
respectively. Cells with no suffixes are string cells.
Custom-mapping using map file
-----------------------------
This process is also very similar to the process we followed for XML mapping.
We first auto-generate a map file, modify it, and use it to do the mapping again.
Since there isn't much difference between XML mapping and JSON mapping, let's
just go through this very quick.
First step is to generate a map file for the auto-detected range by running:
.. code-block::
orcus-json --mode map-gen -o map.json example.json
which will write the mapping rules to ``map.json`` file. When you open the generated
map file, you will see something like the following:
.. code-block:: javascript
{
"sheets": [
"range-0"
],
"ranges": [
{
"sheet": "range-0",
"row": 0,
"column": 0,
"row-header": true,
"fields": [
{
"path": "$[]['id']"
},
{
"path": "$[]['name'][0]"
},
{
"path": "$[]['name'][1]"
},
{
"path": "$[]['active']"
},
{
"path": "$[]['gender']"
},
{
"path": "$[]['language']"
}
],
"row-groups": [
{
"path": "$"
}
]
}
]
}
The structure and content of the map file should look similar to the XML counterpart,
except that it is now in JSON format, and the paths are expressed in slightly
modified JSONPath bracket notation, where ``[]`` represents an array node with
no position specified.
Now that we have a map file, let's modify this and use it to do the mapping once
again. Just like the XML mapping example, we are going to:
* insert two blank rows above,
* drop the ``id`` and ``active`` fields,
* specify labels for the fields, and
* change the sheet name from ``range-0`` to ``My Data``.
This is what we've come up with:
.. code-block:: javascript
{
"sheets": [
"My Data"
],
"ranges": [
{
"sheet": "My Data",
"row": 2,
"column": 0,
"row-header": true,
"fields": [
{
"path": "$[]['name'][0]", "label": "First Name"
},
{
"path": "$[]['name'][1]", "label": "Last Name"
},
{
"path": "$[]['gender']", "label": "Gender"
},
{
"path": "$[]['language']", "label": "Language"
}
],
"row-groups": [
{
"path": "$"
}
]
}
]
}
We'll save this file as ``map-modified.json``, and pass it to the ``orcus-json``
command via ``--map`` or ``-m`` option:
.. code-block::
orcus-json --mode map -o out -f flat -m map-modified.json example.json
Let's check the output in ``out/My Data.txt`` and see what it contains:
.. code-block::
---
Sheet name: My Data
rows: 23 cols: 4
+------------+-------------+--------+----------------+
| | | | |
+------------+-------------+--------+----------------+
| | | | |
+------------+-------------+--------+----------------+
| First Name | Last Name | Gender | Language |
+------------+-------------+--------+----------------+
| Tab | Limpenny | Male | Kazakh |
+------------+-------------+--------+----------------+
| Manda | Hadgraft | Female | Bislama |
+------------+-------------+--------+----------------+
| Mickie | Boreham | Male | Swahili |
+------------+-------------+--------+----------------+
| Celinka | Brookfield | Female | Gagauz |
+------------+-------------+--------+----------------+
| Muffin | Bleas | Female | Hiri Motu |
+------------+-------------+--------+----------------+
| Jackelyn | Crumb | Female | Northern Sotho |
+------------+-------------+--------+----------------+
| Tessie | Hollingsbee | Female | Fijian |
+------------+-------------+--------+----------------+
| Yank | Wernham | Male | Tok Pisin |
+------------+-------------+--------+----------------+
| Brendan | Lello | Male | Fijian |
+------------+-------------+--------+----------------+
| Arabel | Rigg | Female | Kyrgyz |
+------------+-------------+--------+----------------+
| Carolann | McElory | Female | Pashto |
+------------+-------------+--------+----------------+
| Gasparo | Flack | Male | Telugu |
+------------+-------------+--------+----------------+
| Eolanda | Polendine | Female | Kashmiri |
+------------+-------------+--------+----------------+
| Brock | McCaw | Male | Tsonga |
+------------+-------------+--------+----------------+
| Wenda | Espinas | Female | Bulgarian |
+------------+-------------+--------+----------------+
| Zachary | Banane | Male | Persian |
+------------+-------------+--------+----------------+
| Sallyanne | Mengue | Female | Latvian |
+------------+-------------+--------+----------------+
| Elizabet | Hoofe | Female | Tswana |
+------------+-------------+--------+----------------+
| Alastair | Hutchence | Male | Ndebele |
+------------+-------------+--------+----------------+
| Minor | Worland | Male | Dutch |
+------------+-------------+--------+----------------+
The ``id`` and ``active`` fields are gone, the remaining fields have custom
labels we specified, and there are two blank rows above. It appears that all
the changes we have intended have been properly applied.
|
