github.com/Jeffail/benthos/v3@v3.65.0/website/docs/components/processors/sql_select.md (about)

     1  ---
     2  title: sql_select
     3  type: processor
     4  status: stable
     5  categories: ["Integration"]
     6  ---
     7  
     8  <!--
     9       THIS FILE IS AUTOGENERATED!
    10  
    11       To make changes please edit the contents of:
    12       lib/processor/sql_select.go
    13  -->
    14  
    15  import Tabs from '@theme/Tabs';
    16  import TabItem from '@theme/TabItem';
    17  
    18  Runs an SQL select query against a database and returns the result as an array of objects, one for each row returned, containing a key for each column queried and its value.
    19  
    20  Introduced in version 3.59.0.
    21  
    22  
    23  <Tabs defaultValue="common" values={[
    24    { label: 'Common', value: 'common', },
    25    { label: 'Advanced', value: 'advanced', },
    26  ]}>
    27  
    28  <TabItem value="common">
    29  
    30  ```yaml
    31  # Common config fields, showing default values
    32  label: ""
    33  sql_select:
    34    driver: ""
    35    dsn: ""
    36    table: ""
    37    columns: []
    38    where: ""
    39    args_mapping: ""
    40  ```
    41  
    42  </TabItem>
    43  <TabItem value="advanced">
    44  
    45  ```yaml
    46  # All config fields, showing default values
    47  label: ""
    48  sql_select:
    49    driver: ""
    50    dsn: ""
    51    table: ""
    52    columns: []
    53    where: ""
    54    args_mapping: ""
    55    prefix: ""
    56    suffix: ""
    57  ```
    58  
    59  </TabItem>
    60  </Tabs>
    61  
    62  If the query fails to execute then the message will remain unchanged and the error can be caught using error handling methods outlined [here](/docs/configuration/error_handling).
    63  
    64  ## Examples
    65  
    66  <Tabs defaultValue="Table Query (PostgreSQL)" values={[
    67  { label: 'Table Query (PostgreSQL)', value: 'Table Query (PostgreSQL)', },
    68  ]}>
    69  
    70  <TabItem value="Table Query (PostgreSQL)">
    71  
    72  
    73  Here we query a database for columns of footable that share a `user_id`
    74  with the message `user.id`. A [`branch` processor](/docs/components/processors/branch)
    75  is used in order to insert the resulting array into the original message at the
    76  path `foo_rows`:
    77  
    78  ```yaml
    79  pipeline:
    80    processors:
    81      - branch:
    82          processors:
    83            - sql_select:
    84                driver: postgres
    85                dsn: postgres://foouser:foopass@localhost:5432/testdb?sslmode=disable
    86                table: footable
    87                columns: [ '*' ]
    88                where: user_id = ?
    89                args_mapping: '[ this.user.id ]'
    90          result_map: 'root.foo_rows = this'
    91  ```
    92  
    93  </TabItem>
    94  </Tabs>
    95  
    96  ## Fields
    97  
    98  ### `driver`
    99  
   100  A database [driver](#drivers) to use.
   101  
   102  
   103  Type: `string`  
   104  Options: `mysql`, `postgres`, `clickhouse`, `mssql`.
   105  
   106  ### `dsn`
   107  
   108  A Data Source Name to identify the target database.
   109  
   110  #### Drivers
   111  
   112  The following is a list of supported drivers and their respective DSN formats:
   113  
   114  | Driver | Data Source Name Format |
   115  |---|---|
   116  | `clickhouse` | [`tcp://[netloc][:port][?param1=value1&...&paramN=valueN]`](https://github.com/ClickHouse/clickhouse-go#dsn)
   117  | `mysql` | `[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]` |
   118  | `postgres` | `postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]` |
   119  | `mssql` | `sqlserver://[user[:password]@][netloc][:port][?database=dbname&param1=value1&...]` |
   120  
   121  Please note that the `postgres` driver enforces SSL by default, you
   122  can override this with the parameter `sslmode=disable` if required.
   123  
   124  
   125  Type: `string`  
   126  
   127  ```yaml
   128  # Examples
   129  
   130  dsn: tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000
   131  
   132  dsn: foouser:foopassword@tcp(localhost:3306)/foodb
   133  
   134  dsn: postgres://foouser:foopass@localhost:5432/foodb?sslmode=disable
   135  ```
   136  
   137  ### `table`
   138  
   139  The table to query.
   140  
   141  
   142  Type: `string`  
   143  
   144  ```yaml
   145  # Examples
   146  
   147  table: foo
   148  ```
   149  
   150  ### `columns`
   151  
   152  A list of columns to query.
   153  
   154  
   155  Type: `array`  
   156  
   157  ```yaml
   158  # Examples
   159  
   160  columns:
   161    - '*'
   162  
   163  columns:
   164    - foo
   165    - bar
   166    - baz
   167  ```
   168  
   169  ### `where`
   170  
   171  An optional where clause to add. Placeholder arguments are populated with the `args_mapping` field. Placeholders should always be question marks, and will automatically be converted to dollar syntax when the postgres driver is used.
   172  
   173  
   174  Type: `string`  
   175  
   176  ```yaml
   177  # Examples
   178  
   179  where: meow = ? and woof = ?
   180  
   181  where: user_id = ?
   182  ```
   183  
   184  ### `args_mapping`
   185  
   186  An optional [Bloblang mapping](/docs/guides/bloblang/about) which should evaluate to an array of values matching in size to the number of placeholder arguments in the field `where`.
   187  
   188  
   189  Type: `string`  
   190  
   191  ```yaml
   192  # Examples
   193  
   194  args_mapping: root = [ this.cat.meow, this.doc.woofs[0] ]
   195  
   196  args_mapping: root = [ meta("user.id") ]
   197  ```
   198  
   199  ### `prefix`
   200  
   201  An optional prefix to prepend to the query (before SELECT).
   202  
   203  
   204  Type: `string`  
   205  
   206  ### `suffix`
   207  
   208  An optional suffix to append to the select query.
   209  
   210  
   211  Type: `string`  
   212  
   213