Using Spock test how to validate json attributes key-value pairs comes as dynamically inputs

Question

How to write right test, in order to test the below csv data stored in database table. In the input other than item, anything could be optional. In this, item is key, rest all goes as part of json format typically it looks like this in database {"brand": "Brand6", "category": "Category6", "subcategory": "Sub-Category6"}

Input:

item,category,subcategory,brand,type,feature
TEST-ITEM6,Category6,Sub-Category6,Brand6
TEST-ITEM7,Category7,Sub-Category7,Brand7,TYPE7,FEATURE7
TEST-ITEM8,Category8,Sub-Category8,Brand8,TYPE8,FEATURE8

Test case tried:

def "Case 3a. Verify New 2 records with two more additional fields along with earlier fields to the same tenant"() {
        expect:
            sql().eachRow("SELECT * FROM item WHERE item IN ('"+item+"')") { row ->

            def dbItem = row[0]
            def dbAttributes = getJsonToObject(row[1])

            def dbCategory = dbAttributes.getAt("category").toString()
            def dbSubCategory = dbAttributes.getAt("subcategory").toString()
            def dbBrand = dbAttributes.getAt("brand").toString()
            def dbType = dbAttributes.getAt("type").toString()
            def dbFeature = dbAttributes.getAt("feature").toString()

            assert dbItem == item
            assert category == dbCategory
            assert subcategory == dbSubCategory
            assert brand == dbBrand
            assert type == dbType
            assert feature == dbFeature
        }

        where:
        item << ['TEST-ITEM6', 'TEST-ITEM7', 'TEST-ITEM8']
        category << ['Category6','Category7', 'Category8']
        subcategory << ['Sub-Category6','Sub-Category7', 'Sub-Category8']
        brand << ['Brand6','Brand7', 'Brand8']
        type <<  ['TYPE7', 'TYPE8']
        feature << ['FEATURE7', 'FEATURE8']
    }

Error:

Condition not satisfied:

type == dbType
|    |  |
TYPE8|  TYPE7
     false
     1 difference (80% similarity)
     TYPE(8)
     TYPE(7)

Expected :TYPE7

Actual   :TYPE8

Answer 1

For the optional fields you can use the Elvis operator ?: like this (sorry, long code, I modeled your database and two new test cases, one with many optional fields and one failing test):

package de.scrum_master.stackoverflow

import spock.lang.Specification
import spock.lang.Unroll

class DataTableWithOptionalItemsTest extends Specification {
  @Unroll
  def "Case 3a. Verify record '#item' with possibly optional fields"() {
    expect:
    testData[item].each { row ->
      def dbItem = row["item"]
      def dbCategory = row["category"]
      def dbSubCategory = row["subcategory"]
      def dbBrand = row["brand"]
      def dbType = row["type"]
      def dbFeature = row["feature"]

      assert dbItem == item
      assert (category ?: dbCategory) == dbCategory
      assert (subcategory ?: dbSubCategory) == dbSubCategory
      assert (brand ?: dbBrand) == dbBrand
      assert (type ?: dbType) == dbType
      assert (feature ?: dbFeature) == dbFeature
    }

    where:
    item         | category    | subcategory     | brand    | type    | feature
    'TEST-ITEM6' | 'Category6' | 'Sub-Category6' | 'Brand6' | null    | null
    'TEST-ITEM7' | 'Category7' | 'Sub-Category7' | 'Brand7' | 'TYPE7' | 'FEATURE7'
    'TEST-ITEM8' | 'Category8' | 'Sub-Category8' | 'Brand8' | 'TYPE8' | 'FEATURE8'
    'TEST-ITEM9' | null        | null            | null     | null    | null
    'TEST-FAIL'  | 'CategoryX' | 'Sub-CategoryX' | 'BrandX' | 'TYPEX' | 'FEATUREX'
  }

  static final testData = [
    'TEST-ITEM6': [
      [
        item       : 'TEST-ITEM6',
        category   : 'Category6',
        subcategory: 'Sub-Category6',
        brand      : 'Brand6',
        type       : 'dummy',
        feature    : null
      ],
      [
        item       : 'TEST-ITEM6',
        category   : 'Category6',
        subcategory: 'Sub-Category6',
        brand      : 'Brand6',
        type       : null,
        feature    : "foo"
      ]
    ],
    'TEST-ITEM7': [
      [
        item       : 'TEST-ITEM7',
        category   : 'Category7',
        subcategory: 'Sub-Category7',
        brand      : 'Brand7',
        type       : 'TYPE7',
        feature    : 'FEATURE7'
      ],
      [
        item       : 'TEST-ITEM7',
        category   : 'Category7',
        subcategory: 'Sub-Category7',
        brand      : 'Brand7',
        type       : 'TYPE7',
        feature    : 'FEATURE7'
      ]
    ],
    'TEST-ITEM8': [
      [
        item       : 'TEST-ITEM8',
        category   : 'Category8',
        subcategory: 'Sub-Category8',
        brand      : 'Brand8',
        type       : 'TYPE8',
        feature    : 'FEATURE8'
      ],
      [
        item       : 'TEST-ITEM8',
        category   : 'Category8',
        subcategory: 'Sub-Category8',
        brand      : 'Brand8',
        type       : 'TYPE8',
        feature    : 'FEATURE8'
      ]
    ],
    'TEST-ITEM9': [
      [
        item       : 'TEST-ITEM9',
        category   : 'Category1',
        subcategory: 'Sub-Category1',
        brand      : 'Brand1',
        type       : 'TYPE1',
        feature    : 'FEATURE1'
      ],
      [
        item       : 'TEST-ITEM9',
        category   : null,
        subcategory: null,
        brand      : null,
        type       : null,
        feature    : null
      ]
    ],
    'TEST-FAIL' : [
      [
        item       : 'TEST-FAIL',
        category   : 'CategoryX',
        subcategory: 'Sub-CategoryX',
        brand      : 'BrandY',
        type       : 'TYPEX',
        feature    : 'FEATUREX'
      ]
    ]
  ]
}

Answer 2

In this case I would recommend to use Data Tables as it becomes more readable and resembles your input more closely.

And while type and feature are optional, you need to provide some value for it. It could be null or it could be an empty List or Map (if an Item can have more than one type/feature)

So you where block might look like this:

item | category | subcategory | brand | typeFeatureMap
'TEST-ITEM6' | 'Category6' | 'Sub-Category6' | 'Brand6' | [:] // empty
'TEST-ITEM7' | 'Category7' | 'Sub-Category7' | 'Brand7' | ['TYPE7':'FEATURE7']
'TEST-ITEM8' | 'Category8' | 'Sub-Category8' | 'Brand8' | ['TYPE8':'FEATURE8']

I would also recommend to collect the data and then compare it, so you get around ordering issues.

So bofore your eachRow do something like

def itemFeatures = [:]

In your eachRow do something like

itemFeatures.put(dbAttributes.getAt("type").toString(), dbAttributes.getAt("feature").toString())

And afterwards

itemFeatures == typeFeatureMap

While not answering your question, I would recommend to think about separating the tests from your database if possible. If you create separate tests for an database abstraction layer and your business logic, you'll be more happy in the long run ;)