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()ā€

2 Likes

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