I have a SQLite DB that I query like this:
products = cursor.execute(
Select Item,EntryDate,json_group_array(json_object('color',P.option)) as colorarray
.....
).fetchall()
The results of products can look like this
[('dress', '2022-12-27 00:00:00', '[{"color":"blue"},{"color":"green"}]')]
In my jinja template I try to loop over the JSON, but my data is treated like a string and printed out 1 character at a time.
{% for color in products[2] %}
{{ color }}
{% endfor %}
What is the proper way to get this done? I can change my SQLite query to not use json_group_array if there is a more idiomatic option. Also I would rather refer to my data by column names (ex products.colorarray) if possible.
I've tried this query with SQLite and also tried mocking the data.
As said in the comments, products[0][2]
is a string, in fact SQLite's json_object()
returns a serialized JSON, and beacuse strings are iterables you're noticing this:
In my jinja template I try to loop over the JSON, but my data is treated like a string and printed out 1 character at a time.
deserialize products[0][2]
with json.loads()
.
Also I would rather refer to my data by column names (ex products.colorarray) if possible.
Jinja can read a dictionary like this:
d = {
"item": products[0][0],
"timestamp": products[0][1],
"colorarray": json.loads(products[0][2])
}
then get access by the colorarray
key:
{% for color in d["colorarray"] %}
{{ color["color"] }}
{% endfor %}