Snippet Name: Dynamic_JSON_Parsing_SQL

Tags:

Created Date: 2025-06-16 13:12:43
Last Modified Date: 2025-05-31 10:31:50

Dependencies:

DBA Notes:

Sample Output:

Title: Dynamic JSON Parsing SQL
Author: user
Json shredding in SQL server, how can i get a dynamic gramework to identify all the name value pairs

Author: ChatGPT
To dynamically identify all name-value pairs from a JSON object in SQL Server, you can use the `OPENJSON()` function. The `OPENJSON()` function allows you to parse JSON and return data as rows. When you don???t specify a schema, it will return the key-value pairs (property names and their values) from the JSON object.

Here is a framework to dynamically shred a JSON object into name-value pairs:

### Step 1: Parse the JSON using `OPENJSON()`
The following SQL query will extract all name-value pairs from a JSON object:

```sql
DECLARE @json NVARCHAR(MAX) = '{
  "Name": "John",
  "Age": 30,
  "Skills": ["SQL", "C#", "Azure"],
  "Address": {
    "Street": "123 Main St",
    "City": "New York"
  }
}';

SELECT [Key] AS PropertyName, [Value] AS PropertyValue
FROM OPENJSON(@json);
```

### Output:
This will return:
- PropertyName: `"Name"`, PropertyValue: `"John"`
- PropertyName: `"Age"`, PropertyValue: `30`
- PropertyName: `"Skills"`, PropertyValue: `["SQL", "C#", "Azure"]`
- PropertyName: `"Address"`, PropertyValue: `{"Street":"123 Main St","City":"New York"}`

### Step 2: Handle Nested JSON Objects
To handle nested objects or arrays, you need to recursively parse the JSON data. You can use `CROSS APPLY` along with `OPENJSON()` to dive into nested JSON objects:

```sql
DECLARE @json NVARCHAR(MAX) = '{
  "Name": "John",
  "Age": 30,
  "Skills": ["SQL", "C#", "Azure"],
  "Address": {
    "Street": "123 Main St",
    "City": "New York"
  }
}';

-- Extract top-level properties
SELECT j1.[Key] AS PropertyName, 
       j1.[Value] AS PropertyValue
FROM OPENJSON(@json) AS j1
CROSS APPLY (
  -- Extract nested objects or arrays
  SELECT j2.[Key], j2.[Value]
  FROM OPENJSON(j1.[Value]) AS j2
) AS NestedJson
WHERE ISJSON(j1.[Value]) > 0;
```

This framework can be extended further, depending on your use case, such as handling arrays or deeply nested structures.

Would you like to explore additional examples or specific scenarios within JSON shredding?

Author: user
more specifically, i've exported a json export from Trello.com, my shreding so far misses a lot of details, and i cannot seme to extrac them

Author: ChatGPT
User is working with a JSON export from Trello.com and is struggling to extract all details while shredding the JSON in SQL Server.

Author: tool
Model set context updated.

Author: ChatGPT
Trello JSON exports can contain complex, deeply nested structures, including arrays and nested objects, which can be challenging to extract using simple `OPENJSON()` functions. To dynamically shred Trello JSON and capture all details, including nested objects and arrays, a recursive or iterative approach is often needed.

Here???s a more advanced framework that can help with the shredding:

### Step 1: Understand the Trello JSON Structure
Typically, Trello exports JSON with multiple levels of hierarchy. For example:

```json
{
  "name": "My Board",
  "cards": [
    {
      "id": "card1",
      "name": "Card 1",
      "labels": [
        {"name": "label1"},
        {"name": "label2"}
      ],
      "checklists": [
        {
          "name": "Checklist 1",
          "items": [
            {"name": "Task 1", "checked": false},
            {"name": "Task 2", "checked": true}
          ]
        }
      ]
    }
  ],
  "lists": [
    {
      "id": "list1",
      "name": "To Do"
    }
  ]
}
```

In this case, you have to recursively go through each level (e.g., `cards`, `labels`, `checklists`).

### Step 2: Recursive JSON Shredding with `OPENJSON()`

You can use recursive CTEs to handle nested JSON structures. Here???s an example that would work for a complex nested structure:

```sql
DECLARE @json NVARCHAR(MAX) = '{...}'; -- Your Trello JSON export here

-- Recursive CTE to parse all nested levels of the JSON
WITH JsonShredder AS (
    -- Extract top-level properties
    SELECT 
        NULL AS ParentKey,
        j1.[Key] AS PropertyName, 
        j1.[Value] AS PropertyValue,
        CAST(j1.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j1.[Value]) AS IsJson
    FROM OPENJSON(@json) AS j1

    UNION ALL

    -- Recurse into nested JSON objects
    SELECT 
        CONCAT(js.PropertyName, '.', j2.[Key]) AS ParentKey,
        j2.[Key] AS PropertyName, 
        j2.[Value] AS PropertyValue,
        CAST(j2.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j2.[Value]) AS IsJson
    FROM JsonShredder AS js
    CROSS APPLY OPENJSON(js.JsonValue) AS j2
    WHERE js.IsJson > 0
)
-- Select all parsed JSON data
SELECT 
    ISNULL(ParentKey + '.', '') + PropertyName AS FullPropertyName,
    PropertyValue
FROM JsonShredder;
```

### Explanation:
- **Recursive CTE**: The CTE (`JsonShredder`) recurses through all levels of the JSON structure.
- **`OPENJSON()`**: The initial `OPENJSON()` extracts top-level properties. In the recursive part, it further opens nested JSON objects if they exist (`ISJSON()` check).
- **Output**: The query concatenates parent keys with child keys to generate fully qualified paths for the properties (e.g., `cards[0].checklists[0].items[1].name`).

### Step 3: Handle Arrays in Trello JSON
Trello often uses arrays for cards, labels, checklists, etc. The above query flattens objects but may miss array indexes. If you need to include array elements, you can modify the recursive CTE to include array indexing.

Here???s an adjustment to handle arrays properly:

```sql
WITH JsonShredder AS (
    -- Top-level properties
    SELECT 
        NULL AS ParentKey,
        j1.[Key] AS PropertyName, 
        j1.[Value] AS PropertyValue,
        CAST(j1.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j1.[Value]) AS IsJson,
        0 AS ArrayIndex
    FROM OPENJSON(@json) AS j1

    UNION ALL

    -- Recurse into nested JSON objects and arrays
    SELECT 
        CONCAT(js.PropertyName, ISNULL('[' + CAST(js.ArrayIndex AS NVARCHAR(10)) + ']', ''), '.', j2.[Key]) AS ParentKey,
        j2.[Key] AS PropertyName, 
        j2.[Value] AS PropertyValue,
        CAST(j2.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j2.[Value]) AS IsJson,
        js.ArrayIndex
    FROM JsonShredder AS js
    CROSS APPLY OPENJSON(js.JsonValue) AS j2
    WHERE js.IsJson > 0

    UNION ALL

    -- Handle array elements
    SELECT 
        CONCAT(js.PropertyName, '[', j2.[Key], ']') AS ParentKey,
        NULL AS PropertyName, 
        j2.[Value] AS PropertyValue,
        CAST(j2.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j2.[Value]) AS IsJson,
        j2.[Key]
    FROM JsonShredder AS js
    CROSS APPLY OPENJSON(js.JsonValue) AS j2
    WHERE js.IsJson = 1 AND js.PropertyName IS NULL
)
SELECT 
    ISNULL(ParentKey + '.', '') + ISNULL(PropertyName, '') AS FullPropertyName,
    PropertyValue
FROM JsonShredder;
```

This version:
- Adds support for array indexing (e.g., `[0]`, `[1]`).
- Handles nested arrays and objects, ensuring that each array element is identified by its index.

### Step 4: Use Cases for Specific Data
If you need to extract specific details (e.g., all card names or checklist items), you can filter the final output or build more targeted queries.

Would you like to explore a specific use case in your Trello JSON data? Feel free to share more details or a sample JSON structure, and I can help tailor the shredding logic further.

Author: user
i got an error "Types don't match between the anchor and the recursive part in column "ParentKey" of recursive query "JsonShredder"."

Author: ChatGPT
The error occurs because the `ParentKey` column in the anchor part of the recursive CTE (`NULL AS ParentKey`) is of a different data type compared to the recursive part (which concatenates strings). To resolve this, you should explicitly cast `NULL` to the same data type as the concatenated string in the recursive part (e.g., `NVARCHAR(MAX)`).

Here???s the corrected version of the recursive CTE:

```sql
WITH JsonShredder AS (
    -- Top-level properties
    SELECT 
        CAST(NULL AS NVARCHAR(MAX)) AS ParentKey,  -- Explicitly cast NULL to NVARCHAR(MAX)
        j1.[Key] AS PropertyName, 
        j1.[Value] AS PropertyValue,
        CAST(j1.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j1.[Value]) AS IsJson,
        0 AS ArrayIndex
    FROM OPENJSON(@json) AS j1

    UNION ALL

    -- Recurse into nested JSON objects and arrays
    SELECT 
        CONCAT(js.ParentKey, ISNULL('[' + CAST(js.ArrayIndex AS NVARCHAR(10)) + ']', ''), '.', j2.[Key]) AS ParentKey,
        j2.[Key] AS PropertyName, 
        j2.[Value] AS PropertyValue,
        CAST(j2.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j2.[Value]) AS IsJson,
        js.ArrayIndex
    FROM JsonShredder AS js
    CROSS APPLY OPENJSON(js.JsonValue) AS j2
    WHERE js.IsJson > 0

    UNION ALL

    -- Handle array elements
    SELECT 
        CONCAT(js.ParentKey, '[', j2.[Key], ']') AS ParentKey,
        NULL AS PropertyName, 
        j2.[Value] AS PropertyValue,
        CAST(j2.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j2.[Value]) AS IsJson,
        j2.[Key]
    FROM JsonShredder AS js
    CROSS APPLY OPENJSON(js.JsonValue) AS j2
    WHERE js.IsJson = 1 AND js.PropertyName IS NULL
)
SELECT 
    ISNULL(ParentKey + '.', '') + ISNULL(PropertyName, '') AS FullPropertyName,
    PropertyValue
FROM JsonShredder;
```

### Explanation:
- **`CAST(NULL AS NVARCHAR(MAX))`**: Ensures that the data type of `ParentKey` is consistent throughout the recursive CTE.
- **Concatenation**: Handles the recursion through nested JSON objects and arrays by concatenating the `ParentKey` with the current property name or array index.

This adjustment should resolve the data type mismatch error. Let me know if you encounter any further issues or if you need help refining the query for your specific Trello JSON data.

Author: user
i still get the same or similar error: Msg 240, Level 16, State 1, Line 13
Types don't match between the anchor and the recursive part in column "ParentKey" of recursive query "JsonShredder".


Author: ChatGPT
The error persists because there may be further data type mismatches in the recursive part of the query. We need to ensure that every `ParentKey` expression is consistently treated as `NVARCHAR(MAX)` across all parts of the recursive query, including array handling.

Here???s a revised approach with explicit casting throughout the recursive CTE to ensure all parts match:

```sql
WITH JsonShredder AS (
    -- Anchor part: Top-level properties
    SELECT 
        CAST(NULL AS NVARCHAR(MAX)) AS ParentKey,  -- Cast NULL to NVARCHAR(MAX)
        j1.[Key] AS PropertyName, 
        j1.[Value] AS PropertyValue,
        CAST(j1.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j1.[Value]) AS IsJson,
        0 AS ArrayIndex
    FROM OPENJSON(@json) AS j1

    UNION ALL

    -- Recursive part: Recurse into nested JSON objects and arrays
    SELECT 
        CAST(CONCAT(js.ParentKey, ISNULL('[' + CAST(js.ArrayIndex AS NVARCHAR(10)) + ']', ''), '.', j2.[Key]) AS NVARCHAR(MAX)) AS ParentKey,  -- Explicit cast
        j2.[Key] AS PropertyName, 
        j2.[Value] AS PropertyValue,
        CAST(j2.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j2.[Value]) AS IsJson,
        js.ArrayIndex
    FROM JsonShredder AS js
    CROSS APPLY OPENJSON(js.JsonValue) AS j2
    WHERE js.IsJson > 0

    UNION ALL

    -- Recursive part: Handle array elements
    SELECT 
        CAST(CONCAT(js.ParentKey, '[', j2.[Key], ']') AS NVARCHAR(MAX)) AS ParentKey,  -- Explicit cast
        NULL AS PropertyName, 
        j2.[Value] AS PropertyValue,
        CAST(j2.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j2.[Value]) AS IsJson,
        j2.[Key]
    FROM JsonShredder AS js
    CROSS APPLY OPENJSON(js.JsonValue) AS j2
    WHERE js.IsJson = 1 AND js.PropertyName IS NULL
)
SELECT 
    ISNULL(ParentKey + '.', '') + ISNULL(PropertyName, '') AS FullPropertyName,
    PropertyValue
FROM JsonShredder;
```

### Explanation:
- **Explicit Casting**: Every concatenated string for `ParentKey` is explicitly cast to `NVARCHAR(MAX)` using `CAST(...) AS NVARCHAR(MAX)`.
- **`NULL` Handling**: The anchor part is set to `CAST(NULL AS NVARCHAR(MAX))` to match the recursive data type.
- **Consistency**: Ensures that the `ParentKey` is consistently treated as a string (`NVARCHAR(MAX)`) across the recursive CTE.

This should resolve the data type mismatch error. Let me know how it works!

Author: user
the error still persists, it seems: Msg 240, Level 16, State 1, Line 13
Types don't match between the anchor and the recursive part in column "ParentKey" of recursive query "JsonShredder".

Author: ChatGPT
To address this error properly, we need to ensure that every part of the recursive CTE is consistent in terms of data types across all columns, especially `ParentKey`. SQL Server is strict with data type consistency in recursive queries, and it may be necessary to double-check each column for potential mismatches.

Here is a step-by-step approach to ensure consistency and to troubleshoot this issue:

### Step 1: Simplify the Recursive CTE to Identify the Issue
To isolate the issue, start with a simplified recursive CTE without array handling or additional recursion. This will help confirm that the problem lies in data type mismatches.

```sql
WITH JsonShredder AS (
    -- Anchor part: Top-level properties
    SELECT 
        CAST(NULL AS NVARCHAR(MAX)) AS ParentKey,  -- Cast NULL to NVARCHAR(MAX)
        j1.[Key] AS PropertyName, 
        j1.[Value] AS PropertyValue,
        CAST(j1.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j1.[Value]) AS IsJson
    FROM OPENJSON(@json) AS j1

    UNION ALL

    -- Recursive part: Recurse into nested JSON objects
    SELECT 
        CAST(CONCAT(js.ParentKey, '.', j2.[Key]) AS NVARCHAR(MAX)) AS ParentKey,  -- Explicit cast
        j2.[Key] AS PropertyName, 
        j2.[Value] AS PropertyValue,
        CAST(j2.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j2.[Value]) AS IsJson
    FROM JsonShredder AS js
    CROSS APPLY OPENJSON(js.JsonValue) AS j2
    WHERE js.IsJson > 0
)
SELECT 
    ISNULL(ParentKey + '.', '') + ISNULL(PropertyName, '') AS FullPropertyName,
    PropertyValue
FROM JsonShredder;
```

### Explanation:
1. **Simplified Recursion**: This recursive CTE focuses on basic recursion without handling arrays. The `ParentKey` column is explicitly cast to `NVARCHAR(MAX)` at each step.
2. **Troubleshooting**: By simplifying, we can determine if the error persists due to array handling or some other aspect of the query.

### Step 2: Add Debugging Selects
If the simplified query still gives an error, try selecting individual parts of the recursive query to confirm the data types:

```sql
SELECT 
    CAST(NULL AS NVARCHAR(MAX)) AS ParentKey, 
    j1.[Key] AS PropertyName, 
    j1.[Value] AS PropertyValue
FROM OPENJSON(@json) AS j1;
```

And then check:

```sql
WITH JsonShredder AS (
    SELECT 
        CAST(NULL AS NVARCHAR(MAX)) AS ParentKey,
        j1.[Key] AS PropertyName, 
        j1.[Value] AS PropertyValue,
        CAST(j1.[Value] AS NVARCHAR(MAX)) AS JsonValue,
        ISJSON(j1.[Value]) AS IsJson
    FROM OPENJSON(@json) AS j1
)
SELECT * FROM JsonShredder;
```

### Step 3: Reintroduce Array Handling
Once the simplified version works, gradually reintroduce array handling, ensuring that all casts remain consistent. This can help identify which part of the query causes the issue.

Would you like help breaking down your full JSON structure? You can also share a small sample JSON export from Trello if you need more precise guidance.

Author: user
how can i load my json for us to test with

Author: ChatGPT
You can load your JSON data by either:

1. **Pasting a small sample**: If your JSON is not too large, you can paste it directly here, and I'll help you test it with SQL queries.
   
2. **Uploading the JSON file**: If your JSON file is large or complex, you can upload it as a file, and I can then work with the data.

Would you like to upload the JSON file or paste a sample?

Author: user
how can i upload?

Author: ChatGPT
You can upload your JSON file by using the file upload feature. Here???s how:

1. Look for the file upload icon (usually a paperclip or an upload button) in this chat interface.
2. Click on the icon and select your JSON file from your device.
3. Once uploaded, I will process the file and help you shred the JSON in SQL Server.

Feel free to go ahead and upload the file whenever you're ready!

Author: user
does my current ChatGPT licensing module have access to upload? could that be my issue?

Author: ChatGPT
Currently, in this environment, file uploads are not supported. That could indeed be why you couldn't find the option to upload your JSON file.

Instead, you can share a sample of your JSON data directly in the chat (by pasting a snippet). If the JSON is large or complex, you can provide a smaller, representative portion of it, and I can help you work with that sample.

Once you provide the JSON snippet, we can go from there!

Author: user
i am now able to attach json or other attachments. please help me shred this in SQL Server TSQL

Author: tool

Please remember to search the user's documents if an answer to their question is not contained in the above snippets.


Author: ChatGPT
Thank you for uploading the JSON file. The structure looks quite detailed, with nested objects, arrays, and other key-value pairs. Based on what I've seen, let's build a SQL Server T-SQL shredding framework for extracting all the data, including the nested objects.

### Step 1: Open and Parse JSON

You can use `OPENJSON()` with a recursive CTE to handle nested objects and arrays in the JSON. Here's a general approach based on the sample structure:

#### Example for Top-Level Parsing:

```sql
DECLARE @json NVARCHAR(MAX) = '{
    -- Your JSON content here
}';

-- Extract top-level keys
SELECT [Key], [Value]
FROM OPENJSON(@json);
```

### Step 2: Recursive Shredding for Nested Objects

For nested structures, like the `"actions"` array, you can recursively shred them using `CROSS APPLY`:

```sql
WITH JsonShredder AS (
    -- Step 1: Top-level keys
    SELECT 
        CAST(NULL AS NVARCHAR(MAX)) AS ParentKey,
        j.[Key] AS [Key],
        j.[Value] AS [Value],
        ISJSON(j.[Value]) AS IsJson,
        0 AS Level
    FROM OPENJSON(@json) j
    
    UNION ALL

    -- Step 2: Recursively shred nested JSON objects
    SELECT 
        CONCAT(js.ParentKey, '.', js.[Key]) AS ParentKey,
        j.[Key],
        j.[Value],
        ISJSON(j.[Value]) AS IsJson,
        js.Level + 1 AS Level
    FROM JsonShredder js
    CROSS APPLY OPENJSON(js.[Value]) j
    WHERE js.IsJson = 1
)
-- Step 3: Select the full JSON structure with key paths
SELECT 
    CONCAT(ISNULL(ParentKey + '.', ''), [Key]) AS FullPath,
    [Value]
FROM JsonShredder;
```

### Step 3: Extract Specific Data from Arrays

To target specific data, such as from the `"actions"` array, you can drill into arrays like this:

```sql
-- Extract details from 'actions' array
SELECT 
    JSON_VALUE([Value], '$.id') AS ActionId,
    JSON_VALUE([Value], '$.type') AS ActionType,
    JSON_VALUE([Value], '$.date') AS ActionDate,
    JSON_VALUE([Value], '$.data.card.name') AS CardName,
    JSON_VALUE([Value], '$.data.list.name') AS ListName
FROM OPENJSON(@json, '$.actions')
WITH (
    [Value] NVARCHAR(MAX) 'AS JSON'
);
```

### Explanation:
- **Recursive CTE (`JsonShredder`)**: This handles the shredding of nested JSON objects.
- **`ISJSON()`**: Checks if the value is a JSON object or array, allowing recursion.
- **`JSON_VALUE()`**: Extracts specific key-value pairs from JSON arrays or objects.

### Step 4: Adjust for Complex Nested Arrays and Objects
You can repeat the process for deeply nested structures like checklists, attachments, or labels, iterating over arrays and extracting specific data with `JSON_VALUE()`.

This should cover most of the details you want to shred from your Trello JSON. Would you like further customization for specific fields or arrays in your JSON data?

Author: user
can you modify the last query to focus on the array for "card"

Author: ChatGPT
Certainly! Let's modify the query to focus on the `"card"` array within the `actions` array of your JSON data. The `card` data typically includes attributes such as `id`, `name`, and `idShort`, among others.

Here???s how you can adjust the query to extract specific details from the `card` object within the `actions` array:

### Modified Query to Extract from `"card"` Array:
```sql
DECLARE @json NVARCHAR(MAX) = '{
    -- Your JSON content here
}';

-- Extract details from the 'actions' array, focusing on the 'card' object
SELECT 
    JSON_VALUE([Value], '$.id') AS ActionId,
    JSON_VALUE([Value], '$.type') AS ActionType,
    JSON_VALUE([Value], '$.date') AS ActionDate,
    JSON_VALUE([Value], '$.data.card.id') AS CardId,
    JSON_VALUE([Value], '$.data.card.name') AS CardName,
    JSON_VALUE([Value], '$.data.card.idShort') AS CardIdShort,
    JSON_VALUE([Value], '$.data.card.shortLink') AS CardShortLink
FROM OPENJSON(@json, '$.actions')
WITH (
    [Value] NVARCHAR(MAX) 'AS JSON'
);
```

### Explanation:
- **`OPENJSON(@json, '$.actions')`**: Opens the `actions` array in the JSON file.
- **`JSON_VALUE()`**: Extracts specific key-value pairs from within the `card` object inside each action.
  - `$.data.card.id`: Retrieves the card's ID.
  - `$.data.card.name`: Retrieves the card's name.
  - `$.data.card.idShort`: Retrieves the short ID of the card.
  - `$.data.card.shortLink`: Retrieves the short link of the card.
- **Other columns**: We still extract action-level details like `ActionId`, `ActionType`, and `ActionDate`.

This query focuses on the `card` details nested within the `actions` array and should provide a clearer view of the cards being referenced in each action.

Would you like further refinement on additional fields, or does this cover your immediate need?

Author: user
this code that i developed returns four column of helpful information, however, details like The date for elementis int he json like CreateCard and UpdateCard are missing: --#################################################################################################
-- 2024-09-16 06:58:05.595 MachineName:S2WPVDBA1.US1.autonation.com; ComputerBiosName:S2WPVDBA1; 10.10.102.71 | 1433 
-- ApplicationName: DBA | AppEmailGroup: DL-CORP-IT-SQLDBA@autonation.com | ServerUptime[dd:hh:mm:ss]: 09:21:54:18
-- Cname: DBAPRDDB01.US1.autonation.com,1433;  LoginName: US1\IzaguirreL Authorization Scheme: NTLM S2WPVDBA1
-- Context: S2WPVDBA1.US1.autonation.com,1433; Database:DBAResources; Version: [SQL 2022 || 16.0.4135.4] Enterprise Edition: Core-based Licensing (64-bit)
--#################################################################################################
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
DECLARE @json NVARCHAR(MAX) 
SELECT @json = JsonData FROM [DBAResources].dbo.[TrelloBoard] AS [tb]

;WITH CardsLists (Cards,Lists)
AS (
  SELECT Cards
    ,Lists
  FROM OPENJSON(@json) WITH (
      Cards NVARCHAR(max) 'strict $.cards' AS json
      ,Lists NVARCHAR(max) 'strict $.lists' AS json
      )
  )
,CardSet (CardName,CardRowNumber,ListId)
AS (
  SELECT CardName
    ,ROW_NUMBER() OVER (
      PARTITION BY ListID ORDER BY CardPosition
      ) CardRowNumber
    ,
    --you need to partitition by ListID as CardPositions are reusable across lists
    ListID
  FROM CardsLists
  CROSS APPLY OPENJSON(Cards) WITH (
      CardName NVARCHAR(max) '$.name'
      --Like for Lists above, the .pos property defaults to multiples of 65536+1 in Trello JSON documents. 
      ,CardPosition NUMERIC(15, 3) '$.pos'
      --Foreign Key Reference back to the ListID
      ,ListID NVARCHAR(max) '$.idList'
      )
  )
,ListSet (ListID,ListColumnNumber,ListName)
AS (
  SELECT ListId
    ,ListColumnNumber
    ,ListName
  FROM CardsLists
  CROSS APPLY OPENJSON(Lists) WITH (
      Listid NVARCHAR(max) '$.id'
      ,IsClosed  NVARCHAR(max) '$.closed'
      ,ListColumnNumber NUMERIC(15, 3) '$.pos'
      ,listname NVARCHAR(max) '$.name'
      )
  )
  ,CardsListsTable (ListName,ListColumnNumber,CardName,CardRowNumber)
AS (
  SELECT ListName
    ,ListColumnNumber
    ,CardName
    ,CardRowNumber
  FROM CardSet
  INNER JOIN ListSet ON CardSet.ListId = ListSet.ListId
  )
--INSERT INTO #CardsListsTable (ListName,ListColumnNumber,CardName,CardRowNumber)
SELECT 
  ListName
  ,ListColumnNumber
  ,CardName
  ,CardRowNumber
FROM CardsListsTable

Author: ChatGPT
Your query effectively retrieves the list and card information, but you're missing details like the `CreateCard` and `UpdateCard` dates. To capture those, you'll need to extract the information from the `actions` array, where actions like `createCard` and `updateCard` are stored.

I'll modify your query to include the `date` field from `actions` along with the `createCard` and `updateCard` actions.

### Modified Query to Include `CreateCard` and `UpdateCard` Dates

```sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
DECLARE @json NVARCHAR(MAX)
SELECT @json = JsonData FROM [DBAResources].dbo.[TrelloBoard] AS [tb]

;WITH CardsLists (Cards,Lists)
AS (
    SELECT Cards, Lists
    FROM OPENJSON(@json) 
    WITH (
        Cards NVARCHAR(max) 'strict $.cards' AS json,
        Lists NVARCHAR(max) 'strict $.lists' AS json
    )
)
,CardSet (CardName, CardRowNumber, ListId, CardId)
AS (
    SELECT 
        CardName,
        ROW_NUMBER() OVER (PARTITION BY ListID ORDER BY CardPosition) AS CardRowNumber,  -- Partition by ListID as CardPositions are reused across lists
        ListID,
        CardId
    FROM CardsLists
    CROSS APPLY OPENJSON(Cards) 
    WITH (
        CardName NVARCHAR(max) '$.name',
        CardPosition NUMERIC(15, 3) '$.pos',  -- Card position in the list
        ListID NVARCHAR(max) '$.idList',  -- Foreign Key Reference back to ListID
        CardId NVARCHAR(max) '$.id'  -- Unique Card ID
    )
)
,ListSet (ListID, ListColumnNumber, ListName)
AS (
    SELECT 
        ListId,
        ListColumnNumber,
        ListName
    FROM CardsLists
    CROSS APPLY OPENJSON(Lists) 
    WITH (
        Listid NVARCHAR(max) '$.id',
        IsClosed NVARCHAR(max) '$.closed',
        ListColumnNumber NUMERIC(15, 3) '$.pos',
        ListName NVARCHAR(max) '$.name'
    )
)
,ActionDates (CardId, CreateCardDate, UpdateCardDate)
AS (
    -- Extract the action dates for creating and updating cards
    SELECT 
        JSON_VALUE([Value], '$.data.card.id') AS CardId,
        MAX(CASE WHEN JSON_VALUE([Value], '$.type') = 'createCard' THEN JSON_VALUE([Value], '$.date') END) AS CreateCardDate,
        MAX(CASE WHEN JSON_VALUE([Value], '$.type') = 'updateCard' THEN JSON_VALUE([Value], '$.date') END) AS UpdateCardDate
    FROM OPENJSON(@json, '$.actions')
    WITH ([Value] NVARCHAR(max) 'AS JSON')
    GROUP BY JSON_VALUE([Value], '$.data.card.id')
)
,CardsListsTable (ListName, ListColumnNumber, CardName, CardRowNumber, CreateCardDate, UpdateCardDate)
AS (
    SELECT 
        ListName,
        ListColumnNumber,
        CardName,
        CardRowNumber,
        ad.CreateCardDate,
        ad.UpdateCardDate
    FROM CardSet
    INNER JOIN ListSet ON CardSet.ListId = ListSet.ListId
    LEFT JOIN ActionDates ad ON CardSet.CardId = ad.CardId  -- Join to get the action dates
)
-- Select the final data with card creation and update dates
SELECT 
    ListName,
    ListColumnNumber,
    CardName,
    CardRowNumber,
    CreateCardDate,
    UpdateCardDate
FROM CardsListsTable
ORDER BY ListColumnNumber, CardRowNumber;
```

### Changes Made:
1. **Extract `CardId`**: In the `CardSet` CTE, I added the `CardId` column to reference each card by its unique ID.
2. **`ActionDates` CTE**: This CTE extracts the `createCard` and `updateCard` actions from the `actions` array. It uses `JSON_VALUE()` to extract the `CardId` and the respective `date` fields for both `createCard` and `updateCard` events.
3. **Join with `ActionDates`**: I joined the `ActionDates` CTE with the `CardsListsTable` to include the `CreateCardDate` and `UpdateCardDate` in the final result.
4. **Order By**: The final result is ordered by `ListColumnNumber` and `CardRowNumber`.

### Output Columns:
- `ListName`: The name of the list.
- `ListColumnNumber`: The column number (position) of the list.
- `CardName`: The name of the card.
- `CardRowNumber`: The row number of the card within the list.
- `CreateCardDate`: The date when the card was created.
- `UpdateCardDate`: The date when the card was last updated.

This should now return the details of each card, along with its creation and update dates. Let me know if this captures the missing details!

Author: user
the columns produced all have null values; i think because the elements are in a different node. how do i get the date column from actions.data.list.board?

Author: ChatGPT
If the date values are nested under the `actions.data.list.board` structure, the extraction must focus on that specific JSON path. You mentioned that the `date` is stored within a different node, so we need to adjust the query to ensure we're targeting the correct path.

### Updated Plan
We'll target the `actions` array and focus on extracting data from `actions.data.list.board`. Specifically, we'll extract `date` and ensure we're referencing the correct `CardId`, `ListId`, and `BoardId` paths.

### Modified Query

```sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
DECLARE @json NVARCHAR(MAX)
SELECT @json = JsonData FROM [DBAResources].dbo.[TrelloBoard] AS [tb]

;WITH CardsLists (Cards, Lists)
AS (
    SELECT Cards, Lists
    FROM OPENJSON(@json) 
    WITH (
        Cards NVARCHAR(max) 'strict $.cards' AS json,
        Lists NVARCHAR(max) 'strict $.lists' AS json
    )
)
,CardSet (CardName, CardRowNumber, ListId, CardId)
AS (
    SELECT 
        CardName,
        ROW_NUMBER() OVER (PARTITION BY ListID ORDER BY CardPosition) AS CardRowNumber,  -- Partition by ListID as CardPositions are reused across lists
        ListID,
        CardId
    FROM CardsLists
    CROSS APPLY OPENJSON(Cards) 
    WITH (
        CardName NVARCHAR(max) '$.name',
        CardPosition NUMERIC(15, 3) '$.pos',  -- Card position in the list
        ListID NVARCHAR(max) '$.idList',  -- Foreign Key Reference back to ListID
        CardId NVARCHAR(max) '$.id'  -- Unique Card ID
    )
)
,ListSet (ListID, ListColumnNumber, ListName)
AS (
    SELECT 
        ListId,
        ListColumnNumber,
        ListName
    FROM CardsLists
    CROSS APPLY OPENJSON(Lists) 
    WITH (
        Listid NVARCHAR(max) '$.id',
        IsClosed NVARCHAR(max) '$.closed',
        ListColumnNumber NUMERIC(15, 3) '$.pos',
        ListName NVARCHAR(max) '$.name'
    )
)
,ActionDates (CardId, CreateCardDate, UpdateCardDate)
AS (
    -- Extract the action dates for creating and updating cards, focusing on actions.data.list.board
    SELECT 
        JSON_VALUE([Value], '$.data.card.id') AS CardId,
        MAX(CASE WHEN JSON_VALUE([Value], '$.type') = 'createCard' THEN JSON_VALUE([Value], '$.date') END) AS CreateCardDate,
        MAX(CASE WHEN JSON_VALUE([Value], '$.type') = 'updateCard' THEN JSON_VALUE([Value], '$.date') END) AS UpdateCardDate
    FROM OPENJSON(@json, '$.actions')
    WITH (
        [Value] NVARCHAR(max) 'AS JSON'
    )
    -- Ensure we are focusing on actions.data.list.board
    WHERE JSON_VALUE([Value], '$.data.board.id') IS NOT NULL
    GROUP BY JSON_VALUE([Value], '$.data.card.id')
)
,CardsListsTable (ListName, ListColumnNumber, CardName, CardRowNumber, CreateCardDate, UpdateCardDate)
AS (
    SELECT 
        ListName,
        ListColumnNumber,
        CardName,
        CardRowNumber,
        ad.CreateCardDate,
        ad.UpdateCardDate
    FROM CardSet
    INNER JOIN ListSet ON CardSet.ListId = ListSet.ListId
    LEFT JOIN ActionDates ad ON CardSet.CardId = ad.CardId  -- Join to get the action dates
)
-- Select the final data with card creation and update dates
SELECT 
    ListName,
    ListColumnNumber,
    CardName,
    CardRowNumber,
    CreateCardDate,
    UpdateCardDate
FROM CardsListsTable
ORDER BY ListColumnNumber, CardRowNumber;
```

### Key Adjustments:
1. **Filter for `actions.data.board`**: In the `ActionDates` CTE, I included a condition to ensure that we're only working with actions related to a `board`. The path `$.data.board.id` is now checked to ensure we're working with actions that contain `board` information.
2. **Target the Correct JSON Path**: The query is specifically targeting the paths within `actions`, focusing on `$.data.card.id` and `$.data.board.id` to capture the correct `CardId` and the related `CreateCardDate` and `UpdateCardDate`.
3. **Group by `CardId`**: We continue to group by `CardId` to ensure that the correct dates are associated with each card.

### Next Steps:
1. **Check Data Structure**: Ensure that the path to `$.data.board.id` is correct and exists in your Trello JSON. If the path is nested differently, adjust the `JSON_VALUE()` references.
2. **Verify Action Types**: Ensure the action types (like `createCard` and `updateCard`) are being captured correctly. You can also add more action types if needed.

Let me know if this version captures the correct dates!