Converting record to JSON

Ask for Help
1

Hi folks!

Is there a formula to have a column that converts a record to JSON?

Example:

name age JSON (magic formula)
Eduardo 61 {“name”: “Eduardo”, “age”: 61}

Thanks in advance!

Eduardo

2

Grist has a RECORD(rec) function that collects all fields into a Python object: https://support.getgrist.com/functions/#record_2. And you can combine it with Python’s built-in json library:

import json
json.dumps(RECORD(rec, dates_as_iso=True))

The RECORD() function is finicky, as it may cause circular reference errors, and often includes Grist values that are not JSON-serializable. The dates_as_iso argument helps with converting dates to strings (otherwise they would not be JSON-serializable). If the record includes References (also not serializable), RECORD allows you to expand those using expand=1 argument, but doesn’t have an easy way to skip them.

In many cases, it may work better to construct the object manually:

import json
json.dumps(dict(
  Name=$Name,
  Age=$Age
))
2 Likes
3

Thanks @Dmitry_Sagalovskiy ! Works beautifully.

Eduardo

1 Like
4

Any way to adjust the accent?

{“_key”: “jbrj.rb”, “nome”: “Herb\u00e1rio do JBRJ”, “acronimo”: “rb”, “reino”: “plantae”, “escopoTax”: null, “escopoEspacial”: null, “escopoTematico”: “”, “sistema”: “jabot”}

“Herb\u00e1rio do JBRJ” = Herbário do JBRJ"

5

The ensure_ascii option from json — JSON encoder and decoder — Python 3.11.5 documentation looks like it should help.

6

Humm… Looks promising! Where should I put " ensure_ascii=True"

here:

import json
json.dumps(dict(
_key=$id,
nome=$nomeLimpo,
lattesUrl=$Lattes,
lattesId=$LattesID,
grupo=$grupo,
competencia=$permissao.taxa
))

I’ve tried some places without success…

7

It works (formula ok) in here:

import json
json.dumps(dict(
_key=$id,
nome=$nomeLimpo,
lattesUrl=$Lattes,
lattesId=$LattesID,
grupo=$grupo,
competencia=$permissao.taxa
),
ensure_ascii=True)

But still
{“_key”: 1271, “nome”: “Abel Augusto Concei\u00e7\u00e3o”, “lattesUrl”: “”, “lattesId”: “”, “grupo”: [], “competencia”: []}

8

Oh, try ensure_ascii=False instead (True is the default value for it).

1 Like
9

That’s it! Perfect! Thanks!

10

Dear GRIST Folks, @Dimitri_Vlahakis and @anais-grist

That’s pure GOLD! I never tire of being amazed by GRIST. To be able to {organize, clean up, harmonize, verify etc.} data with GRIST and, eventually, easily {move, migrate} this data to another tool, such as MongoDB or ArangoDB, to fulfil specific requirements is a wonderful functionality.

Some people (students) are far more comfortable handling data on spreadsheets, and are afraid to move on with new technologies. They become capped on their capacity to produce significant results on their research. To move to GRIST is great progress on handling their data. But the vision of having GRIST also as a key to open new doors is something else!

Thanks for this!

Eduardo :heart:

3 Likes