Overview

The doc_attribute_values format contains the list of defined attributes values, their main properties and meta-data.

Many attributes don’t need any attribute_values, only the ones for which additional information are required (e.g.: the category tree, some attribute like color which might have a color code as a value, but can connect this attribute value to additional information, …)

Example

In this example, we provide a simple Category Tree structure.

see example in JSON

Here is the example for the first case above (make sure to format it in JSONL before loading to BigQuery: Newline delimited JSON : https://en.wikipedia.org/wiki/JSON_streaming).

{
  "attribute_name": "categories",
  "numerical": true,
  "value_id": "20",
  "value_label": [
    {
      "language": "de",
      "value": "24h-Sortiment"
    }
  ],
  "parent_value_ids": [
    "12"
  ],
  "creation_tm": "2020-10-20 00:00:00",
  "client_id": 1,
  "src_sys_id": 1
}

Properties

Field name	Type	Mode	Description
attribute_name	STRING	REQUIRED	the name of the attribute (should match the name of the attribute provided in the doc_product table in either string_attributes, numeric_attributes or localized_numeric_attributes
numerical	BOOLEAN	NULLABLE	are the provided attribute value identifiers numerical? fyi - in this tables, the values are always provided as strings, but not in the doc_products and other tables
value_id	STRING	REQUIRED	the value id to map to it's related value labels (always a string, but must be castable to numeric if numerical = true)
value_label	LOCALIZED	REPEATED	the labels of the value id
parent_value_ids	STRING	REPEATED	the value ids of the parent (only for hierarchical attributes), (always a string, but must be castable to numeric if numerical = true)
products	PRODUCT	REPEATED	connections to products (normally not needed, as should be provided in the doc_product table
stores	STRING	REPEATED	the stores
title	LOCALIZED	REPEATED	the title of the attribute value
short_description	LOCALIZED	REPEATED	the short description of the product group
description	LOCALIZED	REPEATED	the description of the attribute value
images	LIST	REPEATED	the images of the attribute value
link	LOCALIZED	REPEATED	the link of the attribute value
tags	TAG	REPEATED	the tags , e.g.: [STRUCT('tag', 'hello world', [STRUCT('de', 'hello world')])]
labels	LABEL	REPEATED	the labels of the product line, e.g.: [STRUCT('symbol', 'delivery', '24h', [STRUCT('de', '24-H Versand')])]
pricing	PRICING	NULLABLE	pricing information about the product line, e.g.: STRUCT('discount',[STRUCT('de','Bis:')],[STRUCT('de','-50:')],[STRUCT('de','%')])
visibility	VISIBLITY	REPEATED	the product visibility :VISIBILITY_NOT_VISIBLE = 1; VISIBILITY_IN_CATALOG = 2; VISIBILITY_IN_SEARCH = 3; VISIBILITY_BOTH = 4;
status	STATUS	REPEATED	the product status
periods	PERIOD	REPEATED	information about the activity periods of the attribute value
string_attributes	MAP	REPEATED	additional string (not localized) attributes of the product line (MAP type: STRING)
localized_string_attributes	MAP	REPEATED	additional localized string attributes (MAP type: LOCALIZED in STRING)
numeric_attributes	MAP	REPEATED	additional numeric (not localized) attributes (MAP type: NUMERIC)
localized_numeric_attributes	MAP	REPEATED	additional localized numeric attributes (MAP type: LOCALIZED in NUMERIC)
datetime_attributes	MAP	REPEATED	additional datetime (not localized) attributes (MAP type: DATETIME)
localized_datetime_attributes	MAP	REPEATED	additional localized datetime attributes (MAP type: LOCALIZED inDATETIME)
creation_tm	DATETIME	REQUIRED	technical field
client_id	INTEGER	REQUIRED	technical field
src_sys_id	INTEGER	REQUIRED	technical field

Table structure

BigQuery JSON Schema

[
  {
    "description": "the name of the attribute (should match the name of the attribute provided in the doc_product table in either string_attributes, numeric_attributes or localized_numeric_attributes",
    "type": "STRING",
    "name": "attribute_name",
    "mode": "REQUIRED"
  },
  {
    "type": "BOOLEAN",
    "name": "numerical",
    "description": "are the provided attribute value identifiers numerical? fyi - in this tables, the values are always provided as strings, but not in the doc_products and other tables"
  },
  {
    "description": "the value id to map to it's related value labels (always a string, but must be castable to numeric if numerical = true)",
    "type": "STRING",
    "name": "value_id",
    "mode": "REQUIRED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "language",
        "mode": "REQUIRED"
      },
      {
        "type": "STRING",
        "name": "value",
        "mode": "REQUIRED"
      }
    ],
    "description": "the labels of the value id",
    "type": "RECORD",
    "name": "value_label",
    "mode": "REPEATED"
  },
  {
    "description": "the value ids of the parent (only for hierarchical attributes), (always a string, but must be castable to numeric if numerical = true)",
    "type": "STRING",
    "name": "parent_value_ids",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "type": "STRING",
        "name": "name"
      },
      {
        "type": "STRING",
        "name": "product_line"
      },
      {
        "type": "STRING",
        "name": "product_group"
      },
      {
        "type": "STRING",
        "name": "sku"
      },
      {
        "type": "NUMERIC",
        "name": "value"
      }
    ],
    "description": "connections to products (normally not needed, as should be provided in the doc_product table",
    "type": "RECORD",
    "name": "products",
    "mode": "REPEATED"
  },
  {
    "description": "the stores",
    "type": "STRING",
    "name": "stores",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "language",
        "mode": "REQUIRED"
      },
      {
        "type": "STRING",
        "name": "value",
        "mode": "REQUIRED"
      }
    ],
    "description": "the title of the attribute value",
    "type": "RECORD",
    "name": "title",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "language",
        "mode": "REQUIRED"
      },
      {
        "type": "STRING",
        "name": "value",
        "mode": "REQUIRED"
      }
    ],
    "description": "the short description of the attribute value",
    "type": "RECORD",
    "name": "short_description",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "language",
        "mode": "REQUIRED"
      },
      {
        "type": "STRING",
        "name": "value",
        "mode": "REQUIRED"
      }
    ],
    "description": "the description of the attribute value",
    "type": "RECORD",
    "name": "description",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "name"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "value_id"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "STRING",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "value",
            "mode": "REPEATED"
          }
        ],
        "type": "RECORD",
        "name": "values",
        "mode": "REPEATED"
      }
    ],
    "description": "the images of the attribute value",
    "type": "RECORD",
    "name": "images",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "language"
      },
      {
        "type": "STRING",
        "name": "value",
        "mode": "REQUIRED"
      }
    ],
    "description": "the link of the attribute value",
    "type": "RECORD",
    "name": "link",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "type": "STRING",
        "name": "value"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "value",
            "mode": "REQUIRED"
          }
        ],
        "type": "RECORD",
        "name": "loc_values",
        "mode": "REPEATED"
      }
    ],
    "description": "the tags of the attribute value, e.g.: [STRUCT('tag', 'hello world', [STRUCT('de', 'hello world')])]",
    "type": "RECORD",
    "name": "tags",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "type": "STRING",
        "name": "name",
        "mode": "REQUIRED"
      },
      {
        "type": "STRING",
        "name": "value"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "value",
            "mode": "REQUIRED"
          }
        ],
        "type": "RECORD",
        "name": "loc_values",
        "mode": "REPEATED"
      }
    ],
    "description": "the labels of the attribute value, e.g.: [STRUCT('symbol', 'delivery', '24h', [STRUCT('de', '24-H Versand')])]",
    "type": "RECORD",
    "name": "labels",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "label"
          },
          {
            "type": "STRING",
            "name": "value",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "sign"
          }
        ],
        "type": "RECORD",
        "name": "values",
        "mode": "REPEATED"
      }
    ],
    "description": "pricing information about the attribute value, e.g.: STRUCT('de','discount','Bis:', '-50.-', 'CHF'",
    "type": "RECORD",
    "name": "pricing",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "customer_groups",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "INTEGER",
            "name": "value",
            "mode": "REQUIRED"
          }
        ],
        "type": "RECORD",
        "name": "values",
        "mode": "REPEATED"
      }
    ],
    "description": "the attribute value visibility :VISIBILITY_NOT_VISIBLE = 1; VISIBILITY_IN_CATALOG = 2; VISIBILITY_IN_SEARCH = 3; VISIBILITY_BOTH = 4;",
    "type": "RECORD",
    "name": "visibility",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "language",
        "mode": "REQUIRED"
      },
      {
        "type": "INTEGER",
        "name": "value",
        "mode": "REQUIRED"
      }
    ],
    "description": "the attribute value status",
    "type": "RECORD",
    "name": "status",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "DATETIME",
            "name": "value",
            "mode": "REQUIRED"
          }
        ],
        "type": "RECORD",
        "name": "start_datetime",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "language",
            "mode": "REQUIRED"
          },
          {
            "type": "DATETIME",
            "name": "value",
            "mode": "REQUIRED"
          }
        ],
        "type": "RECORD",
        "name": "end_datetime",
        "mode": "REPEATED"
      }
    ],
    "description": "information about the activity periods of the attribute value",
    "type": "RECORD",
    "name": "periods",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "type": "STRING",
        "name": "name",
        "mode": "REQUIRED"
      },
      {
        "type": "STRING",
        "name": "values",
        "mode": "REPEATED"
      }
    ],
    "description": "additional string (not localized) attributes of the attribute value",
    "type": "RECORD",
    "name": "string_attributes",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "type": "STRING",
        "name": "name",
        "mode": "REQUIRED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "value_id"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "STRING",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "value",
            "mode": "REPEATED"
          }
        ],
        "type": "RECORD",
        "name": "values",
        "mode": "REPEATED"
      }
    ],
    "description": "additional localized string attributes of the attribute value",
    "type": "RECORD",
    "name": "localized_string_attributes",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "type": "STRING",
        "name": "name",
        "mode": "REQUIRED"
      },
      {
        "type": "NUMERIC",
        "name": "values",
        "mode": "REPEATED"
      }
    ],
    "description": "additional numeric (not localized) attributes of the attribute value",
    "type": "RECORD",
    "name": "numeric_attributes",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "type": "STRING",
        "name": "name",
        "mode": "REQUIRED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "value_id"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "NUMERIC",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "value",
            "mode": "REPEATED"
          }
        ],
        "type": "RECORD",
        "name": "values",
        "mode": "REPEATED"
      }
    ],
    "description": "additional localized numeric attributes of the attribute value",
    "type": "RECORD",
    "name": "localized_numeric_attributes",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "type": "STRING",
        "name": "name",
        "mode": "REQUIRED"
      },
      {
        "type": "DATETIME",
        "name": "values",
        "mode": "REPEATED"
      }
    ],
    "description": "additional datetime (not localized) attributes of the attribute value",
    "type": "RECORD",
    "name": "datetime_attributes",
    "mode": "REPEATED"
  },
  {
    "fields": [
      {
        "type": "STRING",
        "name": "type"
      },
      {
        "type": "STRING",
        "name": "name",
        "mode": "REQUIRED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "value_id"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "DATETIME",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "value",
            "mode": "REPEATED"
          }
        ],
        "type": "RECORD",
        "name": "values",
        "mode": "REPEATED"
      }
    ],
    "description": "additional localized datetime attributes of the attribute value",
    "type": "RECORD",
    "name": "localized_datetime_attributes",
    "mode": "REPEATED"
  },
  {
    "description": "technical field",
    "type": "DATETIME",
    "name": "creation_tm",
    "mode": "REQUIRED"
  },
  {
    "description": "technical field",
    "type": "INTEGER",
    "name": "client_id",
    "mode": "REQUIRED"
  },
  {
    "description": "technical field",
    "type": "INTEGER",
    "name": "src_sys_id",
    "mode": "REQUIRED"
  }
]

BigQuery DDL

CREATE OR REPLACE TABLE `YOUR_PROJECT.YOUR_DATASET.doc_attribute_value_F_20201030`   (
	  attribute_name STRING NOT NULL OPTIONS(description="the name of the attribute (should match the name of the attribute provided in the doc_product table in either string_attributes, numeric_attributes or localized_numeric_attributes")
	, numerical BOOLEAN OPTIONS(description="are the provided attribute value identifiers numerical? fyi - in this tables, the values are always provided as strings, but not in the doc_products and other tables")
	, value_id STRING NOT NULL OPTIONS(description="the value id to map to it's related value labels (always a string, but must be castable to numeric if numerical = true)")
	, value_label ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the labels of the value id")
	, parent_value_ids ARRAY<STRING NOT NULL> OPTIONS(description="the value ids of the parent (only for hierarchical attributes), (always a string, but must be castable to numeric if numerical = true)")
	, products ARRAY<STRUCT<type STRING, name STRING, product_line STRING, product_group STRING, sku STRING, value NUMERIC>> OPTIONS(description="connections to products (normally not needed, as should be provided in the doc_product table")
	, stores ARRAY<STRING> OPTIONS(description="the stores")
	, title ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the title of the attribute value")
	, short_description ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the short description of the attribute value")
	, description ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the description of the attribute value")
	, images ARRAY<STRUCT<name STRING, values ARRAY<STRUCT<value_id STRING, value ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>>>> OPTIONS(description="the images of the attribute value")
	, link ARRAY<STRUCT<language STRING, value STRING NOT NULL>> OPTIONS(description="the link of the attribute value")
	, tags ARRAY<STRUCT<type STRING, value STRING, loc_values ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="the tags of the attribute value, e.g.: [STRUCT('tag', 'hello world', [STRUCT('de', 'hello world')])]")
	, labels ARRAY<STRUCT<type STRING, name STRING NOT NULL, value STRING, loc_values ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="the labels of the attribute value, e.g.: [STRUCT('symbol', 'delivery', '24h', [STRUCT('de', '24-H Versand')])]")
	, pricing ARRAY<STRUCT<type STRING, values ARRAY<STRUCT<language STRING NOT NULL, label STRING, value STRING NOT NULL, sign STRING>>>> OPTIONS(description="pricing information about the attribute value, e.g.: STRUCT('de','discount','Bis:', '-50.-', 'CHF'")
	, visibility ARRAY<STRUCT<customer_groups ARRAY<STRING NOT NULL>, values ARRAY<STRUCT<language STRING NOT NULL, value INT64 NOT NULL>>>> OPTIONS(description="the attribute value visibility :VISIBILITY_NOT_VISIBLE = 1; VISIBILITY_IN_CATALOG = 2; VISIBILITY_IN_SEARCH = 3; VISIBILITY_BOTH = 4;")
	, status ARRAY<STRUCT<language STRING NOT NULL, value INT64 NOT NULL>> OPTIONS(description="the attribute value status")
	, periods ARRAY<STRUCT<start_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>, end_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>> OPTIONS(description="information about the activity periods of the attribute value")
	, string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRING NOT NULL>>> OPTIONS(description="additional string (not localized) attributes of the attribute value")
	, localized_string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<value_id STRING, value ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>>>> OPTIONS(description="additional localized string attributes of the attribute value")
	, numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<NUMERIC NOT NULL>>> OPTIONS(description="additional numeric (not localized) attributes of the attribute value")
	, localized_numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<value_id STRING, value ARRAY<STRUCT<language STRING NOT NULL, value NUMERIC NOT NULL>>>>>> OPTIONS(description="additional localized numeric attributes of the attribute value")
	, datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<DATETIME NOT NULL>>> OPTIONS(description="additional datetime (not localized) attributes of the attribute value")
	, localized_datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<value_id STRING, value ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>>>> OPTIONS(description="additional localized datetime attributes of the attribute value")
    , creation_tm   DATETIME NOT NULL OPTIONS(description="technical field")
    , client_id     INT64 NOT NULL OPTIONS(description="technical field")
    , src_sys_id    INT64 NOT NULL OPTIONS(description="technical field")
    )
OPTIONS(description="The attribute values to map string or numeric attributes to (possibly hierarchical) localized attributes");