Skip to main content

Extract values from an attribute with JSON body

Extract values from an attribute with a JSON body using rule boxes in Productsup.

Introduction

If you have a JSON body as a value in an attribute, you may need to extract specific data from it. You can use the following rule boxes to do so:

  • Extract Value from Json scans the JSON structure, finds the needed field, and overwrites the value of the attribute with the data found in that field.

  • Extract with JSONPath scans the JSON structure using the provided query, identifies all matching data, and overwrites the value of the attribute with all matches of the JSONPath.

    Note

    To provide the needed JSONPath in the settings of the rule box, you can use the AI-based JSONPath generator.

Add the rule box Extract Value from Json

If you have a JSON object in an attribute, you can use the rule box Extract Value from Json to extract a value from a specific field within the JSON object.

Note

The rule box Extract Value from Json doesn't work with JSON bodies containing multiple objects.

You can add a rule box in Dataflow and Data View. Here are the steps to add the rule box Extract Value from Json in Data View:

  1. Go to Data View from the site's main menu.

  2. Choose the needed export channel or the intermediate stage in the drop-down menu on the left.

  3. Select Edit in the attribute's column where you want to apply the rule box.

  4. Choose the Select an Option drop-down menu.

  5. Search for and select the Extract Value from Json rule box.

    json.png

  6. In Extract from field, enter the name of the key where you want to extract a value.

  7. Select Save.

For example, if you need to extract the GTIN from the following JSON, enter GTIN in Extract from field to let the platform overwrite the value of the edited attribute with 4028516019993.

{
   "GTIN":"4028516019993",
   "height":"30 MM",
   "color1":"black",
   "color2":"grey",
   "color3":"white"
}

Add the rule box Extract with JSONPath

If you have a JSON body with multiple objects in an attribute, you can use the rule box Extract with JSONPath to extract multiple values from specific fields within the JSON body. See JSONPath Online Evaluator to learn about JSONPath syntax and test it.

You can add a rule box in Dataflow and Data View. Here are the steps to add the rule box Extract with JSONPath in Data View:

  1. Go to Data View from the site's main menu.

  2. Choose the needed export channel or the intermediate stage in the drop-down menu on the left.

  3. Select Edit in the attribute's column where you want to apply the rule box.

  4. Choose the Select an Option drop-down menu.

  5. Search for and select the Extract with JSONPath rule box.

    extract_with_jsonpath.png

  6. In JSONPath, enter the query to filter all keys in the JSON structure and extract the needed values.

    If needed, you can use the AI-based JSONPath generator to create the needed JSONPath.

    1. Select the >_ icon in the top-right corner of the rule box.

      ___icon.png

    2. Describe what you want to extract from the JSON body in the JSONPath generator window and select Generate.

      jsonpath_generator.png

    3. Select Copy and then choose Dismiss.

    4. Paste the copied suggestion into the JSONPath field of the rule box.

  7. In Output format, select how the platform should display the extracted values:

    • JSON overwrites the current value of the edited attribute with the extracted values formatted as a JSON body.

    • Comma-separated list overwrites the current value of the edited attribute with the extracted values separated by commas.

  8. Select Save.

For example, if you need to extract all the GTINs from this JSON body and exclude empty values of the GTIN key from the output, enter $.[?(@.GTIN != " ")].GTIN in JSONPath and choose JSON as your output format to let the platform overwrite the value of the edited attribute with ["4028516019993", "4026402626043"].

[
   {
      "GTIN":"4028516019993",
      "height":"30 MM",
      "color1":"black",
      "color2":"grey",
      "color3":"white"
   },
   {
      "height":"50 MM",
      "GTIN":"4026402626043",
      "color1":"yellow",
      "color2":"orange",
      "color3":""
   },
   {
      "height":"70 MM",
      "GTIN":"",
      "color1":"green",
      "color2":"blue",
      "color3":"purple"
   }
]
In this section: