<commit_before># Flatten

FLATTEN directive separates the elements in a repeated field into individual records.

## Syntax 

```
 flatten <column>[, <column> ]*
```

```column``` name of the column that is a JSON Array.

## Usage Notes

The FLATTEN function is useful for flexible exploration of repeated data.

To maintain the association between each flattened value and the other fields in the record, the FLATTEN function copies all of the other columns into each new record.

In order to understand how FLATTEN directive works in different scenarios, let's look at them with examples. 

### Case 1 

The array in ```col2``` is flattened, the values in ```col3``` is repeated for every value of ```col2```

**INPUT**

|col1|col2|col3|
|----|---- |----|
| A |||
| B |||
|| [x1,y1] | 10 |
|| [x2,y2] | 11 |

**OUTPUT**

|col1|col2|col3|
|----|---- |----|
| A |||
| B |||
|  | x1 | 10 |
|  | y1 | 10 |
|  | x2 | 11 |
|  | y2 | 11 |

### Case 2

The array in ```col2``` and ```col3``` are flattened. 

**INPUT**

|col1|col2|col3|
|----|---- |----|
| A |||
| B |||
|| [x1,y1,z1] | [a1,b1,c1] |
|| [x2,y2] | [a2,b2] |

**OUTPUT**

|col1|col2|col3|
|----|---- |----|
| A |||
| B |||
|  | x1 | a1 |
|  | y1 | b1 |
|  | z1 | c1 |
|  | x2 | a2 |
|  | y2 | b2 |

### Case 3

The array in ```col2``` and ```col3``` are flattened. 

**INPUT**

|col1|col2|col3|
|----|---- |----|
| A |||
| B |||
|| [x1,y1,z1] | [a1,b1] |
|| [x2,y2] | [a2,b2,c2] |

**OUTPUT**

|col1|col2|col3|
|----|---- |----|
| A |||
| B |||
|  | x1 | a1 |
|  | y1 | b1 |
|  | z1 |  |
|  | x2 | a2 |
|  | y2 | b2 |
|  | | c2 |

## Examples

A very simple example would turn this data (one record):

```
{
  "x" : 5,
  "y" : "a string",
  "z" : [ 1,2,3]
}
```

into three distinct records:

| x           | y              | z         |
|-------------|----------------|-----------|
| 5           | "a string"     | 1         |
| 5           | "a string"     | 2         |
| 5           | "a string"     | 3         |<commit_msg>Update flatten.md<commit_after># Flatten

FLATTEN directive separates the elements in a repeated field into individual records.

## Syntax 

```
 flatten <column>[, <column> ]*
```

```column``` name of the column that is a JSON Array.

## Usage Notes

The FLATTEN function is useful for flexible exploration of repeated data.

To maintain the association between each flattened value and the other fields in the record, the FLATTEN function copies all of the other columns into each new record.

In order to understand how FLATTEN directive works in different scenarios, let's look at them with examples. 

### Case 1 

The array in ```col2``` is flattened, the values in ```col3``` is repeated for every value of ```col2```

**INPUT**
```
 [
   { "col1" : "A" },
   { "col1" : "B" },
   { "col2" : [x1, y1], "col3" : 10 },
   { "col2" : [x2, y2], "col3" : 11 } 
 ]   
```
**OUTPUT**
```
 [
   { "col1" : "A" },
   { "col1" : "B" },
   { "col2" : "x1", "col3" : 10 },
   { "col2" : "y1", "col3" : 10 },
   { "col2" : "x2", "col3" : 11 },
   { "col2" : "y2", "col3" : 11 }   
 ]
```

### Case 2

The array in ```col2``` and ```col3``` are flattened. 

**INPUT**
```
 [
   { "col1" : "A" },
   { "col1" : "B" },
   { "col2" : [ "x1", "y1", "z1" ], "col3" : [ "a1", "b1", "c1" ] },
   { "col2" : [ "x2", "y2" ], "col3" : [ "a2", "b2" ] }
 ]
```
**OUTPUT**
```
 [
   { "col1" : "A" },
   { "col1" : "B" },
   { "col2" : "x1", "col3" : "a1" },
   { "col2" : "y1", "col3" : "b1" },
   { "col2" : "z1", "col3" : "c1" },
   { "col2" : "x2", "col3" : "a2" },
   { "col2" : "y2", "col3" : "b2" } 
 ]
```
### Case 3

The array in ```col2``` and ```col3``` are flattened. 

**INPUT**
```
 [
   { "col1" : "A" },
   { "col1" : "B" },
   { "col2" : [ "x1", "y1", "z1" ], "col3" : [ "a1", "b1" ] },
   { "col2" : [ "x2", "y2" ], "col3" : [ "a2", "b2", "c2" ] }
 ]
```
**OUTPUT**
```
 [
   { "col1" : "A" },
   { "col1" : "B" },
   { "col2" : "x1", "col3" : "a1" },
   { "col2" : "y1", "col3" : "b1" },
   { "col2" : "z1" },
   { "col2" : "x2", "col3" : "a2" },
   { "col2" : "y2", "col3" : "b2" },
   { "col3" : "c2" }
 ]
```

## Examples

A very simple example would turn this data (one record):

```
{
  "x" : 5,
  "y" : "a string",
  "z" : [ 1,2,3]
}
```

into three distinct records:

| x           | y              | z         |
|-------------|----------------|-----------|
| 5           | "a string"     | 1         |
| 5           | "a string"     | 2         |
| 5           | "a string"     | 3         |
{'fraction_non_alphanumeric': 0.26109253065774807, 'fraction_numerical': 0.05128205128205128, 'mean_word_length': 2.1502808988764044, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6928798', 'n_tokens_mistral': 2004, 'n_tokens_neox': 1886, 'n_words': 513}