Can a type convert a string to a dict or list?

[davideaster]

I'm using VisiData as a psql pager. Since psql renders to CSV, JSON columns in the database appear as strings in VisiData. It would be useful to be able to convert JSON string columns to their corresponding values.

Is there currently a way to do this?

I've tried defining a type:

from visidata import vd, Sheet, ColumnsSheet, AttrDict
from copy import copy
from json import loads, dumps

def jsonjson(*args):
  if not args:
    return None
  elif not isinstance(args[0], str):
    return copy(args[0])
  else:
    return loads(args[0], object_hook=AttrDict)

jsonjson.__name__ = 'jsonjson'

vd.addType(jsonjson, '{}', formatter=lambda fmtstr,val: dumps(val))

Sheet.addCommand('', 'type-json', 'cursorCol.type=jsonjson', 'set type of current column to JSON')
ColumnsSheet.addCommand('', 'type-json-selected', 'onlySelectedRows.type=jsonjson', 'set type of selected columns to JSON')

This works in that JSON objects are recognized as dicts with the correct keys. However, after expanding the column the expanded fields are empty.

"num", "str", "obj"
1, "one", "{""num"": 1, ""str"": ""one"", ""obj"": {""num"": 1, ""str"": ""one""}}"
2, "two", "{""num"": 2, ""str"": ""two"", ""obj"": {""num"": 2, ""str"": ""two""}}"

$ pbpaste | vd -f csv | csvlook -t # go-right, go-right, type-json, expand-col

| num | str | obj.num | obj.str | obj.obj |
| --- | --- | ------- | ------- | ------- |
|   1 | one |         |         |         |
|   2 | two |         |         |         |

Can a type generate a dict or list this way?

[saulpw]

Well, I tracked this down, and it's due to ExpandedColumn using the original columns' getValue instead of getTypedValue. This is because of the vlen type (z#), which can be applied to lists and dicts and strings, and converts the base value to an integer for sorting and a nicer display; this is used in the Frequency Sheet for the count, which is actually a list of the rows in the bin. Also you can meaningfully apply the str type (~) to get the Python string representation.

You could freeze the column (') after applying your jsonjson type, and then untype the frozen column (z~) but I think it's probably better to do =jsonjson(curcol) or make a command that does the same (and then probably hides the original column).

[davideaster]

Thanks for the explanation, and for the great tool! Treating this as a conversion function is a nice alternative.

[howardrotterdam]

If there is a command to convert/parse a column of JSON string to JSON data object for further expansion with '(' command, that'll be very handy.

[saulpw]

@howardrotterdam =json.loads(curcol)