How to create a new json object based on dynamic values from a DB query?

I am querying my database for an array of products, in my example the results are 2 products. It looks like this:

"query_products": [
    {
      "id": 24,
      "name": "Ebook",
      "description": "Ebook product",
      "original_price": 300,
      "current_price": 200,
      "list_order": null,
      "order_bump": null,
      "deleted": null,
      "mj_property_id": 23558,
      "mj_property_value": "ispurchased"
    },
    {
      "id": 25,
      "name": "audiobook",
      "description": "",
      "original_price": 200,
      "current_price": 100,
      "list_order": null,
      "order_bump": 1,
      "deleted": null,
      "mj_property_id": 23559,
      "mj_property_value": "ispurchased"
    }
  ]

Now I want to create a new JSON object based on these 2 objects (it can be any number or objects!), it should look like this:

{
  "Data": [
    {
      "Name": "23558",
      "Value": "ispurchased"
    },
    {
      "Name": "23559",
      "Value": "ispurchased"
    }
  ]
}

( I’m taking the mj_property_id and mj_property_value )

Context: I need to send this JSON data to another API using ‘API action’:

1 Like

Got it working… but I’d love to know if there’s an easier Wappler way to do this.

Here are my steps:

  1. Query database for my initial products array image

  2. Create an empty array by using ‘Set Value’. Name: Data, Value: {{[]}}

  3. Create a repeat, select your db query as expression

  4. In the repeat, create a ‘Set Value’. Name: obj, Value: {{{Name:mj_property_name,Value:mj_property_value}}} (for me I need an object with two keys: “Name” and “Value”)

  5. In the repeat, create a ‘Set Value’ with the following name and value:

  6. Create a custom formatter with the code:

exports.push = function (arr, obj) {
    return arr.push(obj);
}

(You can use mine as an example:

)

Now to send it with the API Action, I did:
7. Parse the object to JSON… Outside of the repeat, create a new ‘Set Value’ with the following name and value:

  1. Create ‘API Action’ and set the JSON data like:
    {{'{ "Data": '+Data_to_json+' }'}}
1 Like

Thanks buddy, this saved my day! :heart: :+1:

1 Like

Today it can be done more easily.

  1. Make query step to get array from database
  2. Make “group” step
  3. Inside the “group” make a repeat step and bind it to the query.
  4. In repeat properties in “Output fields” choose fields you need. (Or change the “Output filter” to Exclude and leave “Output fields” empty in case you want all fields)
  5. Inside repeat add an empty step, like comment, because repeat can’t be empty.
  6. Finally, add a “Set value” step, bind it to “group” and manually add formatter “toJSON()”

1 Like

Thanks for your reply, @nickneustroev! I will try your method later and report with my findings.

We will be adding soon a special Array List component in Server connect that will make array manipulations more easy. So you can much more easy add, append and manipulate records and output them

5 Likes

In the meantime @Apple, do you know what time is it?

It’s one-liner o´clock! :rofl:

return query_products.map(({mj_property_id: Name, mj_property_value: Value})=>({Name: Name.toString(), Value}))

2 Likes

if there’s an easier Wappler way to do this.

This answer doesn’t meet the qualifications of a ‘Wappler way’ :stuck_out_tongue:

It’s a Wappler extension :joy:

Same as:

1 Like