1

Context

In a QGIS 3.16 project, I have a vector data source defined as a connection to a REST API (Python backend):

REST API vector data source

From the /authors endpoint of the API, I fetch this nested JSON object:

'[{ "id": 1 ,
    "first_name": "John" ,
    "last_name": "Taylor" ,
    "company_name": "ABC" ,
    "address": " Long Street 2" ,
    "zipcode": 226131 ,
    "city": "Bigcity" ,
    "phone": "77 66 55 44 33 22" ,
    "email": "john.taylor@email.org" ,
    "type": "Admin" },
  { "id": 2 ,
    "first_name": "James" ,
    "last_name": "Chester" ,
    "company_name": "ZXY" ,
    "address": "Short Avenue 10" ,
    "zipcode": 226132 ,
    "city": "Smallcity" ,
    "phone": "22 33 44 55 66 77" ,
    "email": "james.chester@email.org" ,
    "type": "Customer" } ]'

For a better comprehension, in the API backend I'm connecting to, the author object is a Python list of dict, each dictionary storing all the characteristics belonging to an author as "key:value" pairs, such as his name, phone, address, etc.

I wish I could display a paragraph as a label on my atlas, using each of the key elements of the dictionaries followed by a colon and then their corresponding value, as such:

id: 1
first_name: John
last_name: Taylor
...
type: Admin

id: 2
first_name: James
last_name: Chester
...
type: Customer

In order to to that, I inserted a text label in my print layout for which I'm trying to use an expression:

print composer text expression menu

Using a native QGIS expression

I actually have something like this (which is not working):

array_foreach(
  from_json("author"),
    array_foreach(
      map_akeys(@element) || ': ' map_get(@element2) # <-- how to differentiate this level-2 element here?
    )
)

And for which I'm facing an issue when trying to extract the key:values information from the embedded dictionaries using two nested array_foreach() loops.
The first one is needed to loop over the elements of the list, i.e. all the dictionaries. And the second one to loop over the "key:value" pairs inside each dictionary, which I'd like to extract and display on my print layout.
The problem is that I cannot differentiate this @element object when it's used in the children array_foreach() loop from the one in the parent loop... And from the doc, this @element is used to represent the current value we are looping onto:

The variable @element will be replaced by the current value.

This is the help on array_foreach() as shown in the QGIS expression composer:

help on array_foreach() in the QGIS expression composer

A small parenthesis here; if I don't use from_json() on the incoming array of authors, QGIS throws me this error, which I don't clearly understand, because the object I'm dealing with should be an array :

Eval Error: Cannot convert '[{ "id": 1, "first_name": "John", "last_name": "Taylor", "company_name": "ABC", "address": " Long Street 2", "zipcode": 226131, "city": "Bigcity", "phone": "77 66 55 44 33 22", "email": "john.taylor@email.org", "type": "Admin" },
  { "id": 2, "first_name": "James", "last_name": "Chester", "company_name": "ZXY", "address": "Short Avenue 10", "zipcode": 226132, "city": "Smallcity", "phone": "22 33 44 55 66 77", "email": "james.chester@email.org", "type": "Customer" } ]'     
to array

But this is not a big deal as using from_json() solved it.

Using a python function as an expression

I also build a Python function, which is perfectly working, but I cannot embed this function into my *.qgs project file! The function itself is stored separately in a *.py file, making the project impossible to easily share.

Question

How can I get the desired formatted output in my layout, as shown above, based on the JSON object given as input that is composed of a list of dictionaries?

  1. using a QGIS native expression, or
  2. by embedding a Python function in my QGIS project file ?
3
  • It seems to be a string (which is technically a list, but not what your want). If you pass it through json.loads(), it would become an array.
    – Vince
    Jun 21 '21 at 18:20
  • Yes, this is what the from_json function from the available functions in the QGIS expression builder seems to actually do, it sounds it's equivalent to your json.loads() (but there is no such json.loads() function) inside the QGIS expression builder. But that's really a minor detail. Jun 23 '21 at 9:57
  • I revised my answer to consider varying length of the input array/map (containing changing number of elements). I hope this works for you?
    – Babel
    Jun 25 '21 at 8:03
2

To generate the following output, based on your JSON object, use the expression from below (see at the bottom under Expression - the second expression contains comments to show how it works). At the beginning, the variable @input is created that takes your JSON object. This allows to use this twice in a simple way, also together with a array_length() function to dynamically adapt it automatically to inputs with changing numbers of elements (maps) contained in it. Alternative would be to define a custom variable (see below as well).

enter image description here

Options

Convert dynamic expression to static output

By the way: since QGIS 3.20, once the expression generates the output you want, you can simply convert the dynamic, or expression-based labels to static text - that allows you to "manually tweak the results when needed." This is especially useful if formatted as html (see next paragraph).

enter image description here

Style as html

Another option would be styling using html - the necessary styling html-tags could be included in the expression. You could replace e.g. array_get (map_akeys( @map),@element) with '<b>' || array_get (map_akeys( @map),@element) || '</b>' to get the key-description formatted as bold (see below for the whole expression):

enter image description here

Create a custom variable for the JSON object

As you see in both expressions, I added your JSON object as a variable to the expression. You could also create a custom variable to define the JSON object there and thus make the expression a bit more straightforward, see: enter image description here

With custom variable and html-formatting, using the second expression from below:

enter image description here

Expression

Expression for normally formatted output

Here is the expression used to generate the output from the first screenshot:

with_variable (
    'input',
    '('' [{ "id": 1, "first_name": "John", "last_name": "Taylor", "company_name": "ABC", "address": " Long Street 2", "zipcode": 226131,"city": "Bigcity", "phone": "77 66 55 44 33 22","email": "john.taylor@email.org", "type": "Admin" },
    {"id": 2, "first_name": "James", "last_name": "Chester", "company_name": "ZXY", "address": "Short Avenue 10", "zipcode": 226132,"city": "Smallcity", "phone": "22 33 44 55 66 77", "email": "james.chester@email.org", "type": "Customer" },
    {"id": 3, "first_name": "Larry", "last_name": "Smith", "company_name": "DEF", "address": " Bright Street 77", "zipcode": 613122,"city": "Tinycity", "phone": "44 33 22 77 66 55 ", "email": "larry.smith@mail.org", "type": "Staff" },
    {"id": 4, "first_name": "Peter", "last_name": "Webber", "company_name": "UVW", "address": "Beatiful Boulevard 88", "zipcode": 872046,"city": "Nicecity", "phone": "66 77 22 33 44 55 ", "email": "peter.webber@nice.web", "type": "Contact" }]''
                     )',
    replace (
        wordwrap (
            replace (
                array_to_string (
                    array_foreach (
                        generate_series (0,array_length (from_json(eval(@input)))),
                        with_variable (
                            'map',
                            array_get (from_json(eval(@input)),@element),
                            array_to_string (
                                array_foreach (
                                    array(5,4,6,2,0,9,1,7,3,8),
                                    array_get (
                                        array_foreach (
                                            generate_series (0,9),
                                            array_get (map_akeys( @map),@element)  || 
                                            ': ' || 
                                            array_get (map_avals( @map),@element)
                                        ),
                                        @element                            
                )))))),
                'id: ',
                ',id: '
            ), 
            1, 
            ','
        ),
        ',',
        ''
))

Expression for output formatted as html

And here is the corresponding expression to create a html-formatted style as in the last screenshot, but this time the variable @input is not part of the expression, but defined as custom variable. Instead, comments are inserted to document how the expression works:

substr(   --line 1: replace the first 4 characters to eliminate the html-line-brake tag <br>, see line 23
    replace (  --line 2: remove the commas generated when the array is converted to text in line 9; see line 21
        array_to_string (   --line 3
            array_foreach (    --line 4: for each element in the list of the JSON object iterate through the following expression
                generate_series (0,array_length (from_json(eval(@input)))),  --line 5: the variable @input is set as context variable, it represents the JOSN object; here it is used to generate a series for each element (entry) in the list of the JSON object
                with_variable ( --line 6: define a variable @map 
                    'map', --line 7: name the variable
                    array_get (from_json(eval(@input)),@element), --line 8: set the value of the variable: it evaluates the @input variable (JSON object), creates an array of it and and from this array the the element N, where N is iterated with the array_foreach function from lines 4/5: so here we get for each iteration one element (author) of the input JSON object
                    array_to_string ( --line 9
                        array_prepend(  --line 10: add another element at the end of the array: a html inebreak tag <br> in line 19: only at the end of each author
                            array_foreach ( --line 11: iterate through each key:value-pair of each element (author) (cf. line 12 and 17)
                                array(5,4,6,2,0,9,1,7,3,8), --line 12: the sort-order of the elements, as the array sorts alphabetically, but we want a custom defined order that is done here with the index of the (alphabetically sorted) array
                                    array_get ( --line 13: now get the N-th element of the array (line 17, defined as @element, refering to the array in line 12
                                        array_foreach ( --line 14 repeat the following steps for each item (key:value pair) of every author
                                            generate_series (0,9), --line 15: as we have 10 attributes for each author, we make a series of 10 integers (starting from 0)
                                            '<b>'  ||  array_get (map_akeys( @map),@element)  || '</b>' || ': ' || array_get (map_avals( @map),@element) || '<br>'),  --line 16: here finally we concatenate different elements like html-tags (for formatting and line-breaks), string/punctuation-elements line colon (:) and getting the string-represenation of each key:value pair: @element refers to the current value of line 15 for each iteration
                                        @element   -- line 17: this time, @element refers to the current value of line 12 for the iteration as we are inside this function here
                            )), -- line 18
                            '<br>'  --line 19 (see comment for line 10)
        )))))  --line 20
        ,',','' -- line 21: arguments from the replace-function in line 2: remove the commas by an empty string (delete commas)
    ), --line 22
    5   --line 23: returns the substring starting from postion 5, thereby cutting the first 4 characters: <br> - what would result in a linebreak before the firs item
) -- line 24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.