How to retrieve results that contain JSON from SQLite for use in a jinja template?-CodePudding

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.

CodePudding user response：

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 %}