![]() The good news is that each one includes a polyfill (or way to create these methods if they are not included in your current JS version) so by adding those in to the top of our script, we can still use this method to get you the results you want. To that extent, I did some research and saw the two methods we needed to make this easy, but of course they are not native to SFMC SSJS: ![]() The example JSON event contains arrays that flatten to lists of nested field names and values. To represent value of type using dot notation, you use userIdentity.type. userIdentity contains a field that's named type. So, my initial thought was similar to Adam Spriggs, but I then thought about that it had potential to be a lot of work if you had multiple DEs that might have different fields you need or you only know the fields you need and might not know the ones you need to remove. The example JSON event contains an object that's named userIdentity. an error as follows (identity-attributes is a JSON Object field). You get NULL value in the output if we replace the JSONMODIFY () function with JSONVALUE (). I want to do a ldapsearch using json attribute organizationOid in the search filter. It retrieves a scalar value from a JSON string. As you know, we cannot use the JSONVALUE () function to retrieve an object or array. Var dataRows = My_DE.Rows.Retrieve(complexFilter) SELECT JSONQUERY(data) AS 'Result' You can see the whole document as part of the JSONQUERY () function. Var My_DE = DataExtension.Init("EXTERNAL KEY HERE") akin to a SELECT FROM and am not sure how such a request might be crafted using a loop on the datarows results.Īny ideas / help - and ideally a working example of a loop if possible - would be much appreciated. I can't find a way to do this in the retrieve process, i.e. PostgreSQL does not support filtering on object key values in arrays.I'm building a SSJS custom API endpoint as a code resource page and have the following code which works fine but I need to limit the fields that are returned from the Data Extension and then placed in the JSON output - it currently returns all fields in the datarows and hence all fields are produced in the JSON array under Data Returned - that's just a dummy name for now. From v2.23.0, but before v4.0.0: advanced Json filtering is a preview feature. The availability of advanced Json filtering depends on your Prisma version: V4.0.0 or later: advanced Json filtering is generally available. Before v2.23.0: you can filter on the exact Json field value, but you cannot use the other features described in this section.Īdvanced Json filtering is supported by PostgreSQL and MySQL only with different syntaxes for the path option. Filter on a Json field From v2.23.0, you can filter rows by the data inside a Json type.From v2.23.0, but before v4.0.0: advanced Json filtering is a preview feature.This allows you to extract the element values. ![]() V4.0.0 or later: advanced Json filtering is generally available. Your filter definition makes such fields available for mapping to variables in actions.The following query uses the operator -> to get all customers in form of JSON: SELECT info ->. The availability of advanced Json filtering depends on your Prisma version: The operator -> returns JSON object field by text. See also: Advanced example: Update a nested JSON key value Filter on a Json fieldįrom v2.23.0, you can filter rows by the data inside a Json type. This will display the JSON response in a modal where you can view, copy, or download it. To view the raw JSON, set up and save your request, and then click Output options > More options > JMESPath > View API response. The bug was in Request mapping for which the workaround as per suggestion to use - Static Body that would help. The first step of working with JMESPath is to view the JSON output of your API request. Note: JavaScript objects (for example, ) are automatically converted to JSON. Response mapping works as per documentation.
0 Comments
Leave a Reply. |