Displaying json data in table

[Katunga]

Hello again!

I am currently trying to display json in a table, which in general works really well!
The issue I'm having is that some columns semm to not display anything while some others display the whole array in a 'json format' for a lack of better words:

As you can see here, the Author and affiliation columns show a white space and the author count display these terms in curly brackets.

I found this other forum entry:
Populating Table with JSON array shows square brackets - Queries and Resources - Retool Forum

which states the following as the solution:

Can you try passing {{query68.data.map(row => _.mapValues(row, _.head))}} to your table?

That should extract all the values from their respective arrays, the lodash documentation has more info on _.mapValues and _.head !

Unfortunately I am not sure how to pass this line to my table or if that would even solve all my issues.

Just in case it might help, here is a snippet of my json:

 "prism:url": "https://api.elsevier.com/content/abstract/scopus_id/85215119954",
        "dc:identifier": "S4",
        "eid": "19954",
        "dc:title": "Construction of an Inquiry Letter Sentiment Dictionary Using SO-PMI and Word2Vec for Sentiment Analysis",
        "dc:creator": "Wang W.",
        "prism:publicationName": "Tehnicki Vjesnik",
        "prism:issn": "13303651",
        "prism:eIssn": "18486339",
        "prism:volume": "32",
        "prism:issueIdentifier": "1",
        "prism:pageRange": "54-65",
        "prism:coverDate": "2025-12-31",
        "prism:coverDisplayDate": "31 December 2025",
        "prism:doi": "10.17559/TV-20230629000774",
        "dc:description": "S",
        "citedby-count": "0",
        "affiliation": [
          {
            "@_fa": "true",
            "affiliation-url": "https://api.elsevier.com/content/affiliation/affiliation_id/60018273",
            "afid": "60018273",
            "affilname": "University of Science and Technology Beijing",
            "affiliation-city": "Beijing",
            "affiliation-country": "China"
          }
        ],
        "prism:aggregationType": "Journal",
        "subtype": "ar",
        "subtypeDescription": "Article",
        "author-count": {
          "@limit": "100",
          "@total": "4",
          "$": "4"
        },
        "author": [
          {
            "@_fa": "true",
            "@seq": "1",
            "author-url": "https://api.elsevier.com/content/author/author_id/57221427227",
            "authid": "57221427227",
            "authname": "Wang W.",
            "surname": "Wang",
            "given-name": "Wei",
            "initials": "W.",
            "afid": [
              {
                "@_fa": "true",
                "$": "60018273"
              }
            ]
          },
          {
            "@_fa": "true",
            "@seq": "2",
            "author-url": "https://api.elsevier.com/content/author/author_id/36095296100",
            "authid": "36095296100",
            "authname": "Wei G.",
            "surname": "Wei",
            "given-name": "Guiying",
            "initials": "G.",
            "afid": [
              {
                "@_fa": "true",
                "$": "60018273"
              }
            ]
          },
          {
            "@_fa": "true",
            "@seq": "3",
            "author-url": "https://api.elsevier.com/content/author/author_id/7407182341",
            "authid": "7407182341",
            "authname": "Wu S.",
            "surname": "Wu",
            "given-name": "Sen",
            "initials": "S.",
            "afid": [
              {
                "@_fa": "true",
                "$": "60018273"
              }
            ]
          },
          {
            "@_fa": "true",
            "@seq": "4",
            "author-url": "",
            "authid": "57221124316",
            "authname": "He H.",
            "surname": "He",
            "given-name": "Huixia",
            "initials": "H.",
            "afid": [
              {
                "@_fa": "true",
                "$": "60018273"
              }
            ]
          }
        ],
        "authkeywords": "",
        "source-id": "14569",
        "fund-acr": "NSFC",
        "fund-no": "22 & ZD153.",
        "fund-sponsor": "",
        "openaccess": "0",
        "openaccessFlag": false
      },
      {
        "@_fa": "true",
        "link": [
          {
            "@_fa": "true",
            "@ref": "self",
            "@href": "https://api.elsevier.com/content/abstract/scopus_id/85215112502"
          },
          {
            "@_fa": "true",
            "@ref": "author-affiliation",
            "@href": "https://api.elsevier.com/content/abstract/scopus_id/85215112502?field=author,affiliation"
          },
          {
            "@_fa": "true",
            "@ref": "scopus",
            "@href": "https://www.scopus.com/inward/record.uri?partnerID=HzOxMe3b&scp=85215112502&origin=inward"
          },
          {
            "@_fa": "true",
            "@ref": "scopus-citedby",
            "@href": "https://www.scopus.com/inward/citedby.uri?partnerID=HzOxMe3b&scp=85215112502&origin=inward"
          }
        ],

Thank you in advance!

[MiguelOrtiz]

Hey there @Katunga,

So, the columns showing JSON format are those for which the data is a simple array. Those that are empty, are because the data is an array of objects.

There's multiple ways of approaching this, but it all depends on what information do you want to show.

For those columns that are a simple array, you can use the "Mapped value" as shown in the screenshot below:

That is, use {{ item }} that allows you to refer to the whole array, and then define which value from the array to use. Remember to change your column's format to an appropriate one so that it doesn't stay in JSON. For example, author count has three keys, so you could create 3 columns, one for $, another for limit and the third one for total

The other case, where you have an array of objects is more complicated. You can use the same technique as above. However, the issue is that you have to select which index to render, as you have many arrays (see below I'm using 0 to then select authName:

My suggestion is not to try and render this data in the table itself, but use either expandable rows. You can add a table to your expandable row and use currenSourceRow.author, see below:

Hope this helps!

[Katunga]

Thank you so, so much! That helped a lot.

I have an additional question:
We sometimes Want to donwload a csv file of the table I just tried that and unfortunately the Author column is still in its array form. Additionally, I'm not sure how it would work with more tahn one column, as the affiliations column has the same issue. Do you know a way to display all the elemtns in such an array in just one column separated by, let's say, a semicolon?

Thank you in advance, you are a great help!

[MiguelOrtiz]

For affiiations column, you can use something like this in the mapped value:

{{ item.map(i => Object.values(i).join("; ")).join(";") }}

[Katunga]

Ohh yes! this would return the whole array separated by semicolons then right?
I didn't know there were so many entries, what I need is just the institutions and maybe the countries. At the bgeinning it returns true. My initial Idea would be to just take leave out the first entries or maybe the first two for each author. Is there a way to do that or do you have a better idea?
Thank you!

[MiguelOrtiz]

Hey @Katunga,

You can return University and country with this:

{{ item.map(i => [Object.values(i)[3], Object.values(i)[4]].join(";")).join(";") }}

You can do the same for the other arrays in the table.

Hope this helps!

[Katunga]

Thank you so much! That worked perfectly