Repeating a varying number of unique values into a JSON output file - how!?

[mgaussie]

Hey all,

I have this JSON I need to output:

{
  "query": {
    "ids": {
      "values": [
        "1",
        "4",
        "100"
      ]
    }
  }
}

In my Server Action I’m querying a table, that returns a number of unique ID’s. The number of unique ID’s will differ, it’s not a fixed number (or this would be easy!).

What I need to do, is include those unique ID’s into the “values”: section. But as the number of records differs, i’m not sure how to do this dynamically.

I’ve tried using a repeat on the query, to ouput jut the unique ID’s. I’ve then tried a set value step to perhaps try to construct the section of JSON and then include that - but I keep getting stuck on the issue of the number of unique ID’s being dynamic.

Love some help here if anyone can recommend how to do this with Wappler.

[Philip_J]

So I’m pretty sure this is what I needed to know a few days ago and figured it. If I understand correctly, the ID’s field will be coming from your DB, and each ID has a number of different values?

Create the query to return IDs
Put a repeat on it, Outputting the id field
Then put your query for the Values in the repeat and put a condition on it to match the ID from the repeat (so you only get one set of values for each ID)

That worked for me.

If you’re having issues with the fact that your values need to be an array of numbers, rather than an array of objects (which is what Wappler server actions create) then I was able to solve that with a custom PostgreSQL query that used the ARRAY function…worked like a charm.

[patrick]

If you get the ids from a sql database then the current result probably looks like:

{
  "query1": [
    { "id": 1 },
    { "id": 4 },
    { "id": 100 }
  ]
}

The server connect formatters are not very up-to-date compared with the ones from app connect, so we are missing the query1.values('id') which would output all id values as an array. As a workaround you can use query1.groupBy('id').keys().

So to get your output, create an action step Set Value with the name query and the following expression as value:

{{ { ids: { values: query1.groupBy('id').keys() } } }}

[mgaussie]

Thank you for this reply @patrick .

I created the Set Value and used your expression, and made sure to map the values - but I’m getting the following error, which stops the entire SC action outputting:

500 Error:
{"status":"500","message":"Cannot read property 'push' of undefined","stack":"TypeError: Cannot read property 'push' of undefined\n at /opt/node_app/lib/formatters/collections.js:64:27\n at Array.reduce (<anonymous>)\n at groupBy (/opt/node_app/lib/formatters/collections.js:61:20)\n at /opt/node_app/lib/core/parser.js:663:26\n at /opt/node_app/lib/core/parser.js:655:32\n at primary (/opt/node_app/lib/core/parser.js:575:39)\n at unary (/opt/node_app/lib/core/parser.js:559:16)\n at multiplicative (/opt/node_app/lib/core/parser.js:537:20)\n at addictive (/opt/node_app/lib/core/parser.js:527:20)\n at bitwiseShift (/opt/node_app/lib/core/parser.js:517:20)"}

Any advice what I’m missing here? (Note NodeJS project, if that helps).

I recreated this exactly as you advised, just to see it working - but I actually need to return a different field from ‘id’ called ‘external_id’ - I imagine it makes no difference to the string?

UPDATE: @patrick - this seems to be something with not initialising an array:

Cannot read property 'push' of undefined when combining arrays

I’ve checked ‘array’ as the Set Value type, I’ve also tried object, text etc… and no luck

[patrick]

How does the output of the database query look like, it is probably different from mine and the expression needs some adjustment.

[mgaussie]

This is the dev tools output for the query:

It’s the ‘external_id’ that I’m trying to get an output I can use in the JSON query in an array so it can handle differing amount of values:

{
  "query": {
    "ids": {
      "values": [
        "0s7ej3EBTyMqS4_KEWUU",
        "0s7ej3EBTyMqS4_GSFWS",
        "0s7ej3EBTyMqS4_KFGSUG"
      ]
    }
  }
}

But in the above error, I mapped it all exactly as per your query, trying to return id not external_id etc.

[patrick]

I see also null values for external_id, these should probably first filtered out.

Try:

{{ { ids: { values: query1.where('external_id', '!=', null).groupBy('external_id').keys() } } }}

[mgaussie]

Thanks Patrick, in a normal scenario - there won’t be null values, those are just there from testing. I’ll go in and add external_id’s to them.

However, I did test your value as is and still get the same push error:

Message: "Cannot read property ‘push’ of undefined"

Is there anything else I can share to get to the bottom of this?

I get the same error in Wappler, too:

[patrick]

Could be a bug in the groupBy formatter, please try following update. Replace in lib/formatters.

collections.zip (1.1 KB)

[mgaussie]

Amazing support, thank you Patrick.

So that looks like it’s fixed that bug, and I’m outputting the values. I just need to figure out how/ where to input this into my JSON output now. I tried just inputting the {{query}} value as the output of the Set Value - but that returned a 400.

Any suggestions for this final part, please?

[mgaussie]

@patrick - just wanted to extend another thanks for your help and the bug fix.

I got this working just now by creating the schema manually in the Set Value and then applying this way:

{
  "query": {
    "ids": {
      "values" : "{{query.ids.values}}"
    }
  }
} 

You my friend, are a superstar.

[Philip_J]

HI @patrick. I was trying this out for myself, but this step changed the sort order of the output (it was sorted by date field in the query, then changed to numerical sorting in the array output)

Is there some syntax to keep the order the same as in the query?

The fields were simply a Date field and a Number field but the order was important to maintain as its for time series forecasting.

[patrick]

Normally the order would not be changed, what is the expression that you use now and how does the data look like?

[Philip_J]

@patrick

ds: { values: query1.groupBy('id').keys() } } }}

I was testing this method when it re-ordered the query. Original query was this:

I then removed the output of that query and added a set Value step like in your example:

{{query1.groupBy('calls').keys()}}

The resulting output does give me just the ‘calls’ data in an array like i was looking for, but it has sorted the call values (an integer) in ASC order. I needed them to maintain their order from the original query which was by their date.

[patrick]

Which server model are you using, PHP or NodeJS?

[Philip_J]

NodeJS