About the CCAP database

Link to the table | Raw data files | SQL Reference | About Google's Big Query

CCAP2: This is the most up to date master table.

Table name	Case types	Date range	Record count	Updated date
master	CM, CV, CF, CT, SC, CI, HL, HT, ME, AD, TP	Jan. 1st, 2005 - Jan, 15th, 2018	8.0 million	Jan, 31th, 2018

CCAP: This table is depreciated. Use CCAP2 instead.

Table name	Case types	Date range	Record count	Updated date
master_all	CV, CM, CF, CT, HL, CI, SC	Varies by case type	3 million	May, 17th, 2016

Tip: Use command+f to search through this documentation.

Querying tips:

Case Types:
CV: Civil, CM: Misdemeanor, CF: Felony, CT: Criminal Traffic, HL: Hospital Lien, CI: Commitment of an Inmate, SC: Small Claims

The data is stored in a hierarchical format. As a result, only leaf nodes can be returned with SELECT statements. For example, to get the case status, use status.descr or to get the county information, use county.countyName, county.countyNo.

Cases are unique by case number (caseNo) and county number (county.countyNo) however the only required field in the database is the case number since sealed cases do not return a county number but instead have the county name in the status description(status.descr).

You can perform case-insensitive queries by appending IGNORE CASE at the end of query. - Docs

If you are receiving an error similar to Cannot query the cross product of repeated fields... then you likely need to flatten the columns mentioned in the error. This can be done using the FLATTEN(table,column) function. - Docs

There are several hundred cases that do not have any case details other than a case number and status description. These are likely sealed or confidential cases.

More details about how this data was generated and loaded into the database at the bottom of this page.

Examples

Count all cases by case type:

SELECT
  type.caseType, Count(*)
FROM
  [CCAP.master_all]
group by type.caseType
                    

Search for cases by court record event text:

SELECT
  caseNo,
  county.countyNo,
  courtRecord.eventDate,
  courtRecord.dktTxt
FROM
  [CCAP.master_all]
WHERE
  courtRecord.dktTxt LIKE '%Milwaukee Journal Sentinel%'
                    

Cases per county:

SELECT
  county.countyNo,
  county.countyName,
  COUNT(*) AS total_cases
FROM
  [CCAP.master_all]
GROUP BY
  county.countyNo,
  county.countyName
ORDER BY
  total_cases DESC
                    

Get all charge statutes for any cases where court record text matches a phrase. Normally this would return a cross product error, but notice the usage of flatten to force those columns into a tabular format.

SELECT
  county.countyNo,
  county.countyName,
  caseNo,
  caption,
  type.caseType,
  charges.statuteCite,
  charges.chargeNo,
  charges.jdgmts.jdgmtSeqNo,
  charges.jdgmts.jdgmtDispoDate,
  charges.jdgmts.dispo.descr,
  courtRecord.dktTxt
FROM
  FLATTEN(FLATTEN(CCAP.master_all, charges), courtRecord)
WHERE
  (type.caseType = 'CF')
  AND courtRecord.dktTxt LIKE '%Milwaukee Journal Sentinel%'
                    

Database fields

Loading the data

This data was generated by querying CCAP's API for each date in the dataset's range as the filing date for each county in Wisconsin.

How this data was loaded into the database:

There are two components: 1) the database structure file and 2) the new-line delimited JSON files.

1. Schema.json: this file tells the database the field names and data types and their heirarchy.

2. ####_allcasedetails.json: these files contain all of the case details for the year (including a few patch files) with each case on a new line.

If something happens to the table it is a fairly quick and easy process to reload the data if needed. Create a new table for the first dataset and append each yearly dataset onto the first year's table. Uploading the same schema.json file for each.

As of the initial load, all datasets can be loaded without any errors. If there is an error, that means there is a field in one of the case details that has not been seen before and is therefore not in the schema.json file. The database will continute loading all of the data in the file except for that one attribute in that one case.

The data folder contains the following case types: CV, CM, CF, CT.

The data2 folder contains the following case types: HL, CI, SC.

Edit this page