Lookup

You can support JSON definitions that provide lookups to compute foreign keys, using natural keys, using the Lookup metadata tag. This is useful for enabling external/partner systems to post data, where your schema uses automatically-generated keys that cannot be known by the sender.

Example: The Demo API

Consider providing an API for partners to place orders. Imagine that the Items include a foreign key to Product, where the key is a DBMS-generated key. External/partner systems know a product name but do not know the product number within Live API Creator.

You can provide an API that accepts product names and not product numbers by:
  • Defining resources that expose the appropriate attributes.
  • Support JSON definitions that provide for lookups to compute foreign keys, so clients do not provide them.
The following resource definition is defined for the Demo API:

JSON

You can PUT the following JSON:

{  
   "@metadata":  {"action":"INSERT"},
   "Customer": {  
      "@metadata": { "action":"LOOKUP",  "key":"Name"},
      "Name":"Alpha and Sons"
   },
   "Items":[  
      {  
         "Product": 
          { "@metadata": {"action":"LOOKUP", "key":"ProductName"},
             "ProductName":"Hammer"
                },
        "Quantity": 1
       }
   ]
}

Lookup Processing

The previous JSON uses the LOOKUP action at two points. Consider the second LOOKUP action, in bold, a lookup for an Item's product_numberThe key property dictates Lookup processing and identifies the fields in the JSON segment (Product) used to lookup the product, as follows:
  1. Live API Creator de-aliases these fields (In the example, ProductName is de-aliased to name).
    Note: The string can be an array of attribute names.
  2. It uses the fields to construct a SQL command to a query on product. The fields do not necessarily need to comprise a primary or unique key, though in practice they typically do.
  3. If a row is returned, Live API Creator assigns the foreign key (which may not necessarily be the "key" fields) to the containing resource level. In this case, the Item's product_number is set.
A similar example is illustrated in the Business to Business Example.

Defined Key

Predefine attributes as part of lookup.

Request Events to Create @metadata

Building on this example, your external/partner system may push back when asked to insert the @metadata tag. You can pre-process, and alter, the incoming request using request events. You can insert the @metadata without requiring it from the client.

For more information about providing entry points using request events, see Extensibility.