qic

QueryInConsole - JSON/YAML/XML command line tool with support of interactive mode.

View on GitHub

doc on github.io

qic - Query In Console

qic show

JSON/YAML/XML comand line query tool with interactive mode.

By design, it tries to keep simple. There’re a lot compromises. It’s not intending to embed a full syntax/sementic engine. For complicated situation, other than introducing very long single line expression, please try code blocks where you can use any python syntax.

This document is available with better format on github.io QiC where colorful text can be used for terminal output.

Installation

python -mpip install qic

run it

qic or python -mqic

Basics

by default it will validate, reformat and color the JSON stream.

“_” means the document root. This is the default value.

(py3) [me@mtp qic]$ cat test/s1.json | qic  # _
[
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000001"
    },
    "product_name": "sildenafil citrate",
    "supplier": "Wisozk Inc",
    "quantity": 261,
    "unit_cost": "$10.47"
  },
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000002"
    },
    "product_name": "Mountain Juniperus ashei",
    "supplier": "Keebler-Hilpert",
    "quantity": 292,
    "unit_cost": "$8.74"
  },
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000003"
    },
    "product_name": "Dextromathorphan HBr",
    "supplier": "Schmitt-Weissnat",
    "quantity": 211,
    "unit_cost": "$20.53"
  }
]

query its conent,

(py3) [me@mtp qic]$ cat test/s1.json | qic  "_[0]"
{
  "_id": {
    "$oid": "5968dd23fc13ae04d9000001"
  },
  "product_name": "sildenafil citrate",
  "supplier": "Wisozk Inc",
  "quantity": 261,
  "unit_cost": "$10.47"
}

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ cat test/s1.json | qic  "_[0]._id"
{
  "$oid": "5968dd23fc13ae04d9000001"
}

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ cat test/s1.json | qic  "_[0].product_name"
sildenafil citrate

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ cat test/s1.json | qic  "_[0].{product_name, quantity, unit_cost}"
{
  "product_name": "sildenafil citrate",
  "quantity": 261,
  "unit_cost": "$10.47"
}

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ cat test/s1.json | qic  "_[]._id"
[
  {
    "$oid": "5968dd23fc13ae04d9000001"
  },
  {
    "$oid": "5968dd23fc13ae04d9000002"
  },
  {
    "$oid": "5968dd23fc13ae04d9000003"
  }
]

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ cat test/s1.json | qic  "_[].{_id,quantity}"
[
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000001"
    },
    "quantity": 261
  },
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000002"
    },
    "quantity": 292
  },
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000003"
    },
    "quantity": 211
  }
]

Check expanded code

(py3) [me@mtp qic]$ cat test/s1.json | qic  "_[]._id" -X
# run : 
[ _umy['_id'] for _umy in _ ]
[
  {
    "$oid": "5968dd23fc13ae04d9000001"
  },
  {
    "$oid": "5968dd23fc13ae04d9000002"
  },
  {
    "$oid": "5968dd23fc13ae04d9000003"
  }
]

(py3) [me@mtp qic]$ cat test/s1.json | qic  "[ _[0].product_name ]" -X
# run : 
[ _[0]['product_name'] ]
[
  "sildenafil citrate"
]

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ cat test/s1.json | qic  "_[].{_id,quantity}" -X
# run : 
[ {'_id':_emt['_id'],'quantity':_emt['quantity']} for _emt in _ ]
[
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000001"
    },
    "quantity": 261
  },
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000002"
    },
    "quantity": 292
  },
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000003"
    },
    "quantity": 211
  }
]


(py3) [me@mtp qic]$ cat test/s1.json | qic  "_[0].{_id,quantity}" -X
# run : 
{'_id':_[0]['_id'],'quantity':_[0]['quantity']}
{
  "_id": {
    "$oid": "5968dd23fc13ae04d9000001"
  },
  "quantity": 261
}

Keys with special chars

look at below changed JSON, product_name is renamed to product.name. This will break the dot expansion QiC is using. In this situation, use <> to mark content within is a single unit.

(py3) [me@mtp qic]$ qic -f test/s1x.json 
[
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000001"
    },
    "product.name": "sildenafil citrate",
    "supplier": "Wisozk Inc",
    "quantity": 261,
    "unit_cost": "$10.47"
  },
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000002"
    },
    "product.name": "Mountain Juniperus ashei",
    "supplier": "Keebler-Hilpert",
    "quantity": 292,
    "unit_cost": "$8.74"
  },
  {
    "_id": {
      "$oid": "5968dd23fc13ae04d9000003"
    },
    "product.name": "Dextromathorphan HBr",
    "supplier": "Schmitt-Weissnat",
    "quantity": 211,
    "unit_cost": "$20.53"
  }
]

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ qic -f test/s1x.json  "_[].product.name"
# expanded code :
[ _kom['product']['name'] for _kom in _ ]
# KeyError: 'product'
(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ qic -f test/s1x.json  "_[].<product.name>"
[
  "sildenafil citrate",
  "Mountain Juniperus ashei",
  "Dextromathorphan HBr"
]

Interactive Mode

-I enable interactive mode.

Qic will read user input from sys.stdin. so, for input stream, it could not be from unix pipe, instead use -f opiton.

when type _ , a small menu is promptec all internal functions started with _.

Before prompted for user input, all keys in the JSON are stored for word completion prompt – as you may have noticed, they’re case insenstive.

(py3) [me@mtp qic]$ qic -f test/s6.json  -I
[qic] $ _
          _          
          _fl        
          _flatlist  
          _j         
          _l         
          _l2pt      
          _l2t       


[qic] $ _.users[0].f
                     firstname  

[qic] $ _.users[0].firstname
Krish

_l2t is an internal function which print “standard” table. \

[qic] $ _l2t(_.users)
userId firstName lastName phoneNumber emailAddress
------------------------------------------------------------------------
1      Krish     Lee      123456      krish.lee@learningcontainer.com
2      racks     jacson   123456      racks.jacson@learningcontainer.com
3      denial    roast    33333333    denial.roast@learningcontainer.com
4      devid     neo      222222222   devid.neo@learningcontainer.com
5      jone      mac      111111111   jone.mac@learningcontainer.com

use ''' to mark code block start and end.

[qic] $
[qic] $ '''
[qic] $ guys = ""
[qic] $ for i in _.users :
[qic] $     guys += i.firstname + i.lastname + ", "
[qic] $ print("List :", guys.rstrip())
[qic] $
[qic] $ '''
List : KrishLee, racksjacson, denialroast, devidneo, jonemac,
[qic] $
[qic] $

use \q or quit() to leave Qic.

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ qic -f test/s6.json  -I
[qic] $
[qic] $ \q
(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ qic -f test/s6.json  -I
[qic] $ quit()
(py3) [me@mtp qic]$

Validate and Convert JSON/XML/YAML

without any parameters, feed input into QiC and it will serve as a format validator ( plus foramatter, etc.).

-t specify source as JSON, YAML or XML. here all examples are from JSON format and JSON is the default type. Choose the right one if you’re going to working with YAML or XML.

Internal function _json or -j will dump output as well formatted JSON and this is the default behaviour.

_yaml or _y will dump well formatted YAML, while _xml or _x will dump well formatted XML.

specify them in format of _x($expr), if for full doc, say, _x(_), just use _x.

(py3) [me@mtp qic]$ qic -f test/s6.json  _x
<?xml version="1.0" ?>
<root>
	<users type="list">
		<item type="dict">
			<userId type="int">1</userId>
			<firstName type="str">Krish</firstName>
			<lastName type="str">Lee</lastName>
			<phoneNumber type="str">123456</phoneNumber>
			<emailAddress type="str">krish.lee@learningcontainer.com</emailAddress>
		</item>
		<item type="dict">
			<userId type="int">2</userId>
			<firstName type="str">racks</firstName>
			<lastName type="str">jacson</lastName>
			<phoneNumber type="str">123456</phoneNumber>
			<emailAddress type="str">racks.jacson@learningcontainer.com</emailAddress>
		</item>
		<item type="dict">
			<userId type="int">3</userId>
			<firstName type="str">denial</firstName>
			<lastName type="str">roast</lastName>
			<phoneNumber type="str">33333333</phoneNumber>
			<emailAddress type="str">denial.roast@learningcontainer.com</emailAddress>
		</item>
		<item type="dict">
			<userId type="int">4</userId>
			<firstName type="str">devid</firstName>
			<lastName type="str">neo</lastName>
			<phoneNumber type="str">222222222</phoneNumber>
			<emailAddress type="str">devid.neo@learningcontainer.com</emailAddress>
		</item>
		<item type="dict">
			<userId type="int">5</userId>
			<firstName type="str">jone</firstName>
			<lastName type="str">mac</lastName>
			<phoneNumber type="str">111111111</phoneNumber>
			<emailAddress type="str">jone.mac@learningcontainer.com</emailAddress>
		</item>
	</users>
</root>

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ qic -f test/s6.json  _y
---
users:
- emailAddress: krish.lee@learningcontainer.com
  firstName: Krish
  lastName: Lee
  phoneNumber: '123456'
  userId: 1
- emailAddress: racks.jacson@learningcontainer.com
  firstName: racks
  lastName: jacson
  phoneNumber: '123456'
  userId: 2
- emailAddress: denial.roast@learningcontainer.com
  firstName: denial
  lastName: roast
  phoneNumber: '33333333'
  userId: 3
- emailAddress: devid.neo@learningcontainer.com
  firstName: devid
  lastName: neo
  phoneNumber: '222222222'
  userId: 4
- emailAddress: jone.mac@learningcontainer.com
  firstName: jone
  lastName: mac
  phoneNumber: '111111111'
  userId: 5

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ qic -f test/s6.json  '_y(_)'
---
users:
- emailAddress: krish.lee@learningcontainer.com
  firstName: Krish
  lastName: Lee
  phoneNumber: '123456'
  userId: 1
- emailAddress: racks.jacson@learningcontainer.com
  firstName: racks
  lastName: jacson
  phoneNumber: '123456'
  userId: 2
- emailAddress: denial.roast@learningcontainer.com
  firstName: denial
  lastName: roast
  phoneNumber: '33333333'
  userId: 3
- emailAddress: devid.neo@learningcontainer.com
  firstName: devid
  lastName: neo
  phoneNumber: '222222222'
  userId: 4
- emailAddress: jone.mac@learningcontainer.com
  firstName: jone
  lastName: mac
  phoneNumber: '111111111'
  userId: 5

(py3) [me@mtp qic]$ 
(py3) [me@mtp qic]$ qic -f test/s6.json  '_y(_.users[:2])'
---
- emailAddress: krish.lee@learningcontainer.com
  firstName: Krish
  lastName: Lee
  phoneNumber: '123456'
  userId: 1
- emailAddress: racks.jacson@learningcontainer.com
  firstName: racks
  lastName: jacson
  phoneNumber: '123456'
  userId: 2

(py3) [me@mtp qic]$

Limit rows

when the embeded list is huge, we may only want to see a few of them.
slice [:$n] can be used for specified list, but -l $n apply to all lists included.

(py3) [me@mtp qic]$ qic -f test/s6.json '_.users' | qic _y
---
- emailAddress: krish.lee@learningcontainer.com
  firstName: Krish
  lastName: Lee
  phoneNumber: '123456'
  userId: 1
- emailAddress: racks.jacson@learningcontainer.com
  firstName: racks
  lastName: jacson
  phoneNumber: '123456'
  userId: 2
- emailAddress: denial.roast@learningcontainer.com
  firstName: denial
  lastName: roast
  phoneNumber: '33333333'
  userId: 3
- emailAddress: devid.neo@learningcontainer.com
  firstName: devid
  lastName: neo
  phoneNumber: '222222222'
  userId: 4
- emailAddress: jone.mac@learningcontainer.com
  firstName: jone
  lastName: mac
  phoneNumber: '111111111'
  userId: 5

(py3) [me@mtp qic]$ qic -f test/s6.json -l2 '_.users' | qic _y
# _[] 5 -> 2
---
- emailAddress: krish.lee@learningcontainer.com
  firstName: Krish
  lastName: Lee
  phoneNumber: '123456'
  userId: 1
- emailAddress: racks.jacson@learningcontainer.com
  firstName: racks
  lastName: jacson
  phoneNumber: '123456'
  userId: 2

(py3) [me@mtp qic]$ qic -f test/s6.json -l2 '_.users[:2]' 
[
  {
    "userId": 1,
    "firstName": "Krish",
    "lastName": "Lee",
    "phoneNumber": "123456",
    "emailAddress": "krish.lee@learningcontainer.com"
  },
  {
    "userId": 2,
    "firstName": "racks",
    "lastName": "jacson",
    "phoneNumber": "123456",
    "emailAddress": "racks.jacson@learningcontainer.com"
  }
]

load extra modules

load module/class/function from python source files.

yonghang@mtp~ $ cat ~/tmp/tm.py 

def xxlen(ds) :
    return str(len(ds)) + " : " + str(ds)

yonghang@mtp~ $ curl -s walkerever.com/share/test/json/s6.json | qic -m ~/tmp/tm.py "tm.xxlen(_)"
1 : {'users': [{'userId': 1, 'firstName': 'Krish', 'lastName': 'Lee', 'phoneNumber': '123456', 'emailAddress': 'krish.lee@learningcontainer.com'}, {'userId': 2, 'firstName': 'racks', 'lastName': 'jacson', 'phoneNumber': '123456', 'emailAddress': 'racks.jacson@learningcontainer.com'}, {'userId': 3, 'firstName': 'denial', 'lastName': 'roast', 'phoneNumber': '33333333', 'emailAddress': 'denial.roast@learningcontainer.com'}, {'userId': 4, 'firstName': 'devid', 'lastName': 'neo', 'phoneNumber': '222222222', 'emailAddress': 'devid.neo@learningcontainer.com'}, {'userId': 5, 'firstName': 'jone', 'lastName': 'mac', 'phoneNumber': '111111111', 'emailAddress': 'jone.mac@learningcontainer.com'}]}

you can load more,

yonghang@mtp~ $ curl -s walkerever.com/share/test/json/s6.json | qic -m ~/tmp/tm.py,json,yaml "tm.xxlen(_)" 
1 : {'users': [{'userId': 1, 'firstName': 'Krish', 'lastName': 'Lee', 'phoneNumber': '123456', 'emailAddress': 'krish.lee@learningcontainer.com'}, {'userId': 2, 'firstName': 'racks', 'lastName': 'jacson', 'phoneNumber': '123456', 'emailAddress': 'racks.jacson@learningcontainer.com'}, {'userId': 3, 'firstName': 'denial', 'lastName': 'roast', 'phoneNumber': '33333333', 'emailAddress': 'denial.roast@learningcontainer.com'}, {'userId': 4, 'firstName': 'devid', 'lastName': 'neo', 'phoneNumber': '222222222', 'emailAddress': 'devid.neo@learningcontainer.com'}, {'userId': 5, 'firstName': 'jone', 'lastName': 'mac', 'phoneNumber': '111111111', 'emailAddress': 'jone.mac@learningcontainer.com'}]}

JSON to HTML

_h and _zh are two functions for the convertion. _h is to create plain HTML table while _zh makes collapse/expand possible.

yonghang@mtp~ $ curl -s walkerever.com/share/test/json/s6.json | qic "_h"
<tt>
<table style="border-collapse:collapse;;" border=1 >
<tr class="child-MPzoqOdFrYXQpqJzvSpl">
<td valign="top"><b>users</b></td>
    <td>
        <table style="border-collapse:collapse;;" border=1 width="100%">
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>userId</b></td>
            <td>1</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>firstName</b></td>
            <td>Krish</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>lastName</b></td>
            <td>Lee</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>phoneNumber</b></td>
            <td>123456</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>emailAddress</b></td>
            <td>krish.lee@learningcontainer.com</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>userId</b></td>
            <td>2</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>firstName</b></td>
            <td>racks</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>lastName</b></td>
            <td>jacson</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>phoneNumber</b></td>
            <td>123456</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>emailAddress</b></td>
            <td>racks.jacson@learningcontainer.com</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>userId</b></td>
            <td>3</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>firstName</b></td>
            <td>denial</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>lastName</b></td>
            <td>roast</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>phoneNumber</b></td>
            <td>33333333</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>emailAddress</b></td>
            <td>denial.roast@learningcontainer.com</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>userId</b></td>
            <td>4</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>firstName</b></td>
            <td>devid</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>lastName</b></td>
            <td>neo</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>phoneNumber</b></td>
            <td>222222222</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#F1F1F1">
            <td valign="top"><b>emailAddress</b></td>
            <td>devid.neo@learningcontainer.com</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>userId</b></td>
            <td>5</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>firstName</b></td>
            <td>jone</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>lastName</b></td>
            <td>mac</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>phoneNumber</b></td>
            <td>111111111</td>
        </tr>
        <tr class="child-WvkznPHasGyCxViXFeQH" style="background-color:#FFFFFF">
            <td valign="top"><b>emailAddress</b></td>
            <td>jone.mac@learningcontainer.com</td>
        </tr>
        </table>

    </td>
</tr>
</table>

_h in HTML,

users
userId 1
firstName Krish
lastName Lee
phoneNumber 123456
emailAddress krish.lee@learningcontainer.com
userId 2
firstName racks
lastName jacson
phoneNumber 123456
emailAddress racks.jacson@learningcontainer.com
userId 3
firstName denial
lastName roast
phoneNumber 33333333
emailAddress denial.roast@learningcontainer.com
userId 4
firstName devid
lastName neo
phoneNumber 222222222
emailAddress devid.neo@learningcontainer.com
userId 5
firstName jone
lastName mac
phoneNumber 111111111
emailAddress jone.mac@learningcontainer.com

_zh table,

D(1)
users
L(5)
userId 1
firstName Krish
lastName Lee
phoneNumber 123456
emailAddress krish.lee@learningcontainer.com
userId 2
firstName racks
lastName jacson
phoneNumber 123456
emailAddress racks.jacson@learningcontainer.com
userId 3
firstName denial
lastName roast
phoneNumber 33333333
emailAddress denial.roast@learningcontainer.com
userId 4
firstName devid
lastName neo
phoneNumber 222222222
emailAddress devid.neo@learningcontainer.com
userId 5
firstName jone
lastName mac
phoneNumber 111111111
emailAddress jone.mac@learningcontainer.com

… to be continued.