<p>If you know there's only one row you're interested in, you can execute the query with <a href="https://docs.craftcms.com/api/v3/craft-db-query.html#method-one" rel="nofollow noreferrer"><code>.one()</code></a> instead of <code>.array()</code>, this will give you a single result row as an associative array instead of an array of rows. In your case, since you're only interested in a single column, you can also use <a href="https://docs.craftcms.com/api/v3/craft-db-query.html#method-scalar" rel="nofollow noreferrer"><code>.scalar()</code></a> to return that column directly as a string. Once you have the string, you can decode the JSON string using the <a href="https://craftcms.com/docs/3.x/dev/filters.html#json-decode" rel="nofollow noreferrer"><code>json_decode</code> filter</a>. Then you can access the value you're interested in.</p>
<p>To handle the case where the result row doesn't exist, check if the query didn't return <code>null</code> or <code>false</code> before attempting to decode the result.</p>
<pre class="lang-twig prettyprint-override"><code>{% set orderFields = craft.query()
    .select('response')
    .from('commerce_transactions')
    .scalar()
%}
{% set orderFields = orderFields ? orderFields|json_decode : null %}
{% set pmethod = orderFields ? orderFields.pmethod : null %}
</code></pre>
<p><strong>Query filtering</strong></p>
<p>Your query doesn't apply any conditions or sorting, so it will just fetch ALL transactions (which might be a huge dataset once you have a couple of customers). Or if you're executing the query with <code>.scalar()</code> or <code>.one()</code>, it will give you just one (essentially) random row as the result.</p>
<p>In order to get the row you need, you need to add conditions to the query that uniquely identify that row. For a start, you can filter by rows that have the text <code>01</code> in their <code>code</code> field:</p>
<pre class="lang-twig prettyprint-override"><code>{% set orderFields = craft.query()
    .select('response')
    .from('commerce_transactions')
    .where(['code' =&gt; '01'])
    .scalar()
%}
</code></pre>
<p>Since most of the data in that table is contained in the string as JSON, filtering based on that might be a little difficult. In this case, you can also fetch multiple transactions and then use the <a href="https://twig.symfony.com/doc/3.x/filters/filter.html" rel="nofollow noreferrer"><code>filter</code> filter</a> to find the one you need. Something like this:</p>
<pre class="lang-twig prettyprint-override"><code>{% set orderFields = craft.query()
    .select('response')
    .from('commerce_transactions')
    .all()
    |map(row =&gt; row | json_decode)
%}
{% set orderFieldWithPaymentInfo = orderFields
    |filter(row =&gt; row.authorizationCode == '534711419')
    |first
%}
{% set pmethod = orderFieldWithPaymentInfo.pmethod %}
</code></pre>
<hr />
<p>This method is really error-prone and I don't think it's the right approach. Craft Commerce provides services and and hooks to access data (such as transactions) at specific points in the order process, and you should probably use those. It's rarely necessary to bypass those APIs and query the database directly. This is bad practice in particular because then you're tightly coupling your plugin logic to implementation details of Craft Commerce that might change at any point.</p>
<p>Take a look at the <a href="https://craftcms.com/docs/commerce/3.x/" rel="nofollow noreferrer">documentation for Craft Commerce</a> to find the best way to get the data you're looking for.</p>

CC BY-SA 4.0
2022-04-14T04:56:16.463Z
i get en error, "Key "pmethod" for array with keys "amount, currency,..." does not exist. Is this because, in the first row/item in the table, there is no pmethod in the json object? only every second/third row has this pmethod (they have "success" in the status column).
Henrik
CC BY-SA 4.0
2022-04-14T07:29:20.870Z
@Henrik Yes, `scalar()` returns the first column of the first row that matches so query, so if that one doesn't contain the `pmethod` field you'll get an error. In this case, either adjust your query to only find the row you're looking for, or execute the query with [`column()`](https://docs.craftcms.com/api/v3/craft-db-query.html#public-methods) instead. This will return an array of strings, then you can iterate through that to find the row you're looking for.
MoritzLost
CC BY-SA 4.0
2022-04-14T13:18:19.143Z
thanks. getting a bit lost here. with 'column()' you mean the twig filter?  so i have to set another variable from orderFields? or how can i adjust the query to get the row i am looking for?
Henrik
CC BY-SA 4.0
2022-04-14T14:00:07.050Z
@Henrik No, I mean execute the query with `column()`. See [this method here](https://docs.craftcms.com/api/v3/craft-db-query.html#method-column). Instead of putting `scalar()` at the end of your query, use `column()`. This will return an array of rows that match the query. It's difficult to find a good solution without the larger picture. Maybe post some additional context to your question – where does the data come from, and who puts it there? Why are there multiple rows that match your query? What exactly are you looking for there?
MoritzLost
CC BY-SA 4.0
2022-04-14T14:00:57.857Z
@Henrik You can probably add additional conditions to the query so it only finds exactly the row you're looking for. But without knowing the context I can't tell you what those conditions may be.
MoritzLost
CC BY-SA 4.0
2022-04-20T07:43:23.063Z
a added additional info to my question, hope it opens up a larger picture of what i try to achieve... so in theory, i have to get all row with the code column of "01" first, then get into the "response" column to get the pmethod key, right?
Henrik
CC BY-SA 4.0
2022-04-20T10:38:46.273Z
@Henrik Yes, sounds right. I've added some examples on how to apply this condition to the query and/or filter the result set further if needed. Though I don't think you're on the right track with that query, there's probably an easier way to access the data you're looking for using the API provided by Craft Commerce.
MoritzLost
CC BY-SA 4.0
2022-04-13T10:03:43.083Z
2022-04-20T10:36:47.410Z
2022-04-20T10:36:47.410Z
MoritzLost
<p>in commerce, all transactions are written into the database, into the table &quot;commerce_transactions&quot;. every transactions will result in a row, where different data is the stored into columns.
(id, gateway, amount, ..).</p>
<p>i have a plugin which provides a gateway for a swiss payment provider. When somebody pays over this gateway a column called &quot;response&quot; is filled with a json object, which includes also the amount, the id, and pmethod (basically which card is used).</p>
<p>short example:</p>
<p>{&quot;currency&quot;:&quot;CHF&quot;,&quot;amount&quot;:&quot;6000&quot;,&quot;pmethod&quot;:&quot;PFC&quot;,&quot;status&quot;:&quot;success&quot;}</p>
<p>because of a redirect, each transaction results in two rows.
i am interested in the row, which has a 01 in the code column, because only this row has the &quot;pmethod&quot; key in the &quot;response&quot; column. (see screenshot)
<a href="https://i.sstatic.net/x2xCb.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/x2xCb.png" alt="enter image description here" /></a></p>
<p>i get the data with this:</p>
<pre><code>{% set orderFields = craft.query().select('response').from('commerce_transactions').all() %}
</code></pre>
<p>but i need only one key from the object, the pmethod. i want to output &quot;PFC&quot;, or whatever is written there for outputting it via twig in the confirmation customer email.</p>
<p>how do i get this one key pair?</p>

CC BY-SA 4.0
2022-04-13T08:25:23.660Z
What is the query returning at the moment? Try putting `{% dd orderFields %}` in there to see the result of the query. Also, what kind of column is this `response`, a custom column in a custom table or a regular field of an entry?
MoritzLost
CC BY-SA 4.0
2022-04-13T08:26:35.310Z
its a custom column of a custom table.
Henrik
CC BY-SA 4.0
2022-04-13T08:30:23.073Z
Can you post the result of the `dd` statement? Also, try using `asArray()` on the query and/or `one()` instead of `.all()`.
MoritzLost
CC BY-SA 4.0
2022-04-13T08:30:51.250Z
the result of the query via dd (shortened version):
[
    0 => [
        'response' => '{\"amount\":\"3192\",\"pmethod\":\"ECA\",\"status\":\"success\"}'
    ]
]
Henrik
CC BY-SA 4.0
2022-04-13T08:35:20.950Z
.asArray() gives me an error: Calling unknown method: craft\db\Query::asArray()
Henrik
CC BY-SA 4.0
2022-04-13T10:04:17.827Z
Ah ok, asArray doesn't exist because it's not element query, so it isn't populated in the first place. Anyway, I've added an answer that should work below.
MoritzLost
<plugin-craftcommerce><database><json>
How to retrieve (& output via twig) one key pair of json object stored i a database column
# How to retrieve (& output via twig) one key pair of json object stored i a database column

(Asked by: username_0 on 2022-04-13)

in commerce, all transactions are written into the database, into the table "commerce_transactions". every transactions will result in a row, where different data is the stored into columns.
(id, gateway, amount, ..).
i have a plugin which provides a gateway for a swiss payment provider. When somebody pays over this gateway a column called "response" is filled with a json object, which includes also the amount, the id, and pmethod (basically which card is used).
short example:
{"currency":"CHF","amount":"6000","pmethod":"PFC","status":"success"}
because of a redirect, each transaction results in two rows.
i am interested in the row, which has a 01 in the code column, because only this row has the "pmethod" key in the "response" column. (see screenshot)

i get the data with this:
{% set orderFields = craft.query().select('response').from('commerce_transactions').all() %}

but i need only one key from the object, the pmethod. i want to output "PFC", or whatever is written there for outputting it via twig in the confirmation customer email.
how do i get this one key pair?

**Tags:** plugin-craftcommerce, database, json
> **Comments:**
> **username_1**: What is the query returning at the moment? Try putting `{% dd orderFields %}` in there to see the result of the query. Also, what kind of column is this `response`, a custom column in a custom table or a regular field of an entry?  
> **username_0**: its a custom column of a custom table.  
> **username_1**: Can you post the result of the `dd` statement? Also, try using `asArray()` on the query and/or `one()` instead of `.all()`.  
> **username_0**: the result of the query via dd (shortened version): [     0 => [         'response' => '{\"amount\":\"3192\",\"pmethod\":\"ECA\",\"status\":\"success\"}'     ] ]  
> **username_0**: .asArray() gives me an error: Calling unknown method: craft\db\Query::asArray()  
> **username_1**: Ah ok, asArray doesn't exist because it's not element query, so it isn't populated in the first place. Anyway, I've added an answer that should work below.  

---

## 1 answers

### Answer by username_1 on 2022-04-13. Score: 3

If you know there's only one row you're interested in, you can execute the query with .one() instead of .array(), this will give you a single result row as an associative array instead of an array of rows. In your case, since you're only interested in a single column, you can also use .scalar() to return that column directly as a string. Once you have the string, you can decode the JSON string using the json_decode filter. Then you can access the value you're interested in.
To handle the case where the result row doesn't exist, check if the query didn't return null or false before attempting to decode the result.
{% set orderFields = craft.query()
    .select('response')
    .from('commerce_transactions')
    .scalar()
%}
{% set orderFields = orderFields ? orderFields|json_decode : null %}
{% set pmethod = orderFields ? orderFields.pmethod : null %}

Query filtering
Your query doesn't apply any conditions or sorting, so it will just fetch ALL transactions (which might be a huge dataset once you have a couple of customers). Or if you're executing the query with .scalar() or .one(), it will give you just one (essentially) random row as the result.
In order to get the row you need, you need to add conditions to the query that uniquely identify that row. For a start, you can filter by rows that have the text 01 in their code field:
{% set orderFields = craft.query()
    .select('response')
    .from('commerce_transactions')
    .where(['code' => '01'])
    .scalar()
%}

Since most of the data in that table is contained in the string as JSON, filtering based on that might be a little difficult. In this case, you can also fetch multiple transactions and then use the filter filter to find the one you need. Something like this:
{% set orderFields = craft.query()
    .select('response')
    .from('commerce_transactions')
    .all()
    |map(row => row | json_decode)
%}
{% set orderFieldWithPaymentInfo = orderFields
    |filter(row => row.authorizationCode == '534711419')
    |first
%}
{% set pmethod = orderFieldWithPaymentInfo.pmethod %}


This method is really error-prone and I don't think it's the right approach. Craft Commerce provides services and and hooks to access data (such as transactions) at specific points in the order process, and you should probably use those. It's rarely necessary to bypass those APIs and query the database directly. This is bad practice in particular because then you're tightly coupling your plugin logic to implementation details of Craft Commerce that might change at any point.
Take a look at the documentation for Craft Commerce to find the best way to get the data you're looking for.

> **Comments:**
> **username_0**: i get en error, "Key "pmethod" for array with keys "amount, currency,..." does not exist. Is this because, in the first row/item in the table, there is no pmethod in the json object? only every second/third row has this pmethod (they have "success" in the status column).  
> **username_1**: @Henrik Yes, `scalar()` returns the first column of the first row that matches so query, so if that one doesn't contain the `pmethod` field you'll get an error. In this case, either adjust your query to only find the row you're looking for, or execute the query with [`column()`](https://docs.craftcms.com/api/v3/craft-db-query.html#public-methods) instead. This will return an array of strings, then you can iterate through that to find the row you're looking for.  
> **username_0**: thanks. getting a bit lost here. with 'column()' you mean the twig filter?  so i have to set another variable from orderFields? or how can i adjust the query to get the row i am looking for?  
> **username_1**: @Henrik No, I mean execute the query with `column()`. See [this method here](https://docs.craftcms.com/api/v3/craft-db-query.html#method-column). Instead of putting `scalar()` at the end of your query, use `column()`. This will return an array of rows that match the query. It's difficult to find a good solution without the larger picture. Maybe post some additional context to your question – where does the data come from, and who puts it there? Why are there multiple rows that match your query? What exactly are you looking for there?  
> **username_1**: @Henrik You can probably add additional conditions to the query so it only finds exactly the row you're looking for. But without knowing the context I can't tell you what those conditions may be.  
> **username_0**: a added additional info to my question, hope it opens up a larger picture of what i try to achieve... so in theory, i have to get all row with the code column of "01" first, then get into the "response" column to get the pmethod key, right?  
> **username_1**: @Henrik Yes, sounds right. I've added some examples on how to apply this condition to the query and/or filter the result set further if needed. Though I don't think you're on the right track with that query, there's probably an easier way to access the data you're looking for using the API provided by Craft Commerce.  

---

