Convert json array to comma separated string php

SQL Query to get the json array values by comma separated,Explanation. In your question you want to get the values of options array of the JSON in to comma separated string. So the steps of the solution is : Step 1. Get the array data from JSON: Here by using -> operator we are able to fetch the data from data field. data->'$.options' will return the array in JSON format. Step 2.,How to convert a JSON array to comma separated string in MySQL?,How to convert MySQL JSON array to comma separated string

i think this is the most only MySQL clean way, atleast for MySQL versions under 8. Query. SET SESSION group_concat_max_len = @@max_allowed_packet; SELECT GROUP_CONCAT[ JSON_UNQUOTE[ JSON_EXTRACT[records.json, CONCAT['$[', number_generator.number , ']']] ] ] FROM [ SELECT @row := @row + 1 AS number FROM [ …

["+63[02]3647766", "+63[02]5467329", "+63[02]8555522", "+63[02]3642403"]

["+63[02]3647766", "+63[02]5467329", "+63[02]8555522", "+63[02]3642403"]

+63[02] 3647766, +63[02] 5467329, +63[02] 8555522, +63[02] 3642403

+63[02]3647766,+63[02]5467329,+63[02]8555522,+63[02]3642403

SET SESSION group_concat_max_len = @ @max_allowed_packet;

SELECT
GROUP_CONCAT[
   JSON_UNQUOTE[
      JSON_EXTRACT[records.json, CONCAT['$[', number_generator.number, ']']]
   ]
]

FROM[

   SELECT @row: = @row + 1 AS number FROM[
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
   ] row1 CROSS JOIN[
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
   ] row2 CROSS JOIN[
      SELECT @row: = -1
   ] init_user_params
] AS number_generator
CROSS JOIN[

   SELECT json, JSON_LENGTH[records.json] AS json_array_length FROM[

      SELECT '["+63[02]3647766", "+63[02]5467329", "+63[02]8555522", "+63[02]3642403"]'
      AS json FROM DUAL
   ] AS records

] AS records
WHERE
number BETWEEN 0 AND json_array_length - 1

Suggestion : 2

I have a string column in an SQL-Server table on Azure which contains the following data:,How can it be transformed into a comma-separated string containing "Jane, John"?,Here how to achieve this via a snippet of some of my older code, you should be able to loop through the table and do this for each row. There may be a quicker way of doing it but this will work.

 {
    "status": "success",
    "data": [{
          "name": "Jane",
          "type": 0
       },
       {
          "name": "John",
          "type": 0
       }
    ]
 }

DECLARE @JSON NVARCHAR[200] = '{"status": "success", "data": [{"name": "Jane", "type": 0},{"name": "John", "type": 0}]}',
   @result nvarchar[max] = ''

SELECT @result = @result + [value] + N ', '
FROM[
   SELECT DISTINCT data.value FROM OPENJSON[@JSON, '$.data'] as jsondata CROSS APPLY OPENJSON[jsondata.value] as data WHERE data.[key] = 'name'] a

select @result = substring[@result, 1, [LEN[@result] - 1]]

select @result

Suggestion : 3

A JSON array contains a list of values separated by commas and enclosed within [ and ] characters: , A JSON object contains a set of key-value pairs separated by commas and enclosed within { and } characters: , Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error. , Normalization is also performed when values are inserted into JSON columns, as shown here:

A JSON array contains a list of values separated by commas and enclosed within [ and ] characters:

["abc", 10, null, true, false]

A JSON object contains a set of key-value pairs separated by commas and enclosed within { and } characters:

{
   "k1": "value",
   "k2": 10
}

As the examples illustrate, JSON arrays and objects can contain scalar values that are strings or numbers, the JSON null literal, or the JSON boolean true or false literals. Keys in JSON objects must be strings. Temporal [date, time, or datetime] scalar values are also permitted:

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

Attempting to insert a value into a JSON column succeeds if the value is a valid JSON value, but fails if it is not:

mysql > CREATE TABLE t1[jdoc JSON];
Query OK, 0 rows affected[0.20 sec]

mysql > INSERT INTO t1 VALUES['{"key1": "value1", "key2": "value2"}'];
Query OK, 1 row affected[0.01 sec]

mysql > INSERT INTO t1 VALUES['[1, 2,'];
ERROR 3140[22032] at line 2: Invalid JSON text:
   "Invalid value."
at position 6 in value[or column]
'[1, 2,'.

The JSON_TYPE[] function expects a JSON argument and attempts to parse it into a JSON value. It returns the value's JSON type if it is valid and produces an error otherwise:

mysql > SELECT JSON_TYPE['["a", "b", 1]']; +
-- -- -- -- -- -- -- -- -- -- -- -- -- -- +
|
JSON_TYPE['["a", "b", 1]'] |
   + -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
   |
   ARRAY |
   + -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

   mysql > SELECT JSON_TYPE['"hello"']; +
-- -- -- -- -- -- -- -- -- -- -- +
|
JSON_TYPE['"hello"'] |
   + -- -- -- -- -- -- -- -- -- -- -- +
   |
   STRING |
   + -- -- -- -- -- -- -- -- -- -- -- +

   mysql > SELECT JSON_TYPE['hello'];
ERROR 3146[22032]: Invalid data type
for JSON data in argument 1
to
function json_type;
a JSON string or JSON type is required.

The previous example does not work as shown if the NO_BACKSLASH_ESCAPES server SQL mode is enabled. If this mode is set, a single backslash instead of double backslashes can be used to insert the JSON object literal, and the backslashes are preserved. If you use the JSON_OBJECT[] function when performing the insert and this mode is set, you must alternate single and double quotes, like this:

mysql > INSERT INTO facts VALUES >
   [JSON_OBJECT['mascot', 'Our mascot is a dolphin named "Sakila".']];

When a string is parsed and found to be a valid JSON document, it is also normalized. This means that members with keys that duplicate a key found later in the document, reading from left to right, are discarded. The object value produced by the following JSON_OBJECT[] call includes only the second key1 element because that key name occurs earlier in the value, as shown here:

mysql > SELECT JSON_OBJECT['key1', 1, 'key2', 'abc', 'key1', 'def']; +
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
|
JSON_OBJECT['key1', 1, 'key2', 'abc', 'key1', 'def'] |
   + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
   |
   {
      "key1": "def",
      "key2": "abc"
   } |
   + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

Normalization is also performed when values are inserted into JSON columns, as shown here:

mysql > CREATE TABLE t1[c1 JSON];

mysql > INSERT INTO t1 VALUES >
   ['{"x": 17, "x": "red"}'], >
   ['{"x": 17, "x": "red", "x": [3, 5, 7]}'];

mysql > SELECT c1 FROM t1; +
-- -- -- -- -- -- -- -- -- +
|
c1 |
   + -- -- -- -- -- -- -- -- -- +
   |
   {
      "x": "red"
   } |
   |
   {
      "x": [3, 5, 7]
   } |
   + -- -- -- -- -- -- -- -- -- +

In versions of MySQL prior to 8.0.3, members with keys that duplicated a key found earlier in the document were discarded. The object value produced by the following JSON_OBJECT[] call does not include the second key1 element because that key name occurs earlier in the value:

mysql > SELECT JSON_OBJECT['key1', 1, 'key2', 'abc', 'key1', 'def']; +
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
|
JSON_OBJECT['key1', 1, 'key2', 'abc', 'key1', 'def'] |
   + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
   |
   {
      "key1": 1,
      "key2": "abc"
   } |
   + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

Suggestion : 4

Pingback: Converting comma separated fields to MySQL JSON – a case study – PipisCrew Official Homepage ,Converting to a JSON array,1st step: remove the leading commas,One way it is called materialized path, which consist with values separated by a delimiter, in my case a comma ,.

To search an specific element the query would be:

SELECT
parent_id,
user_id,
depth,
asc_path,
node
FROM tree
WHERE asc_path LIKE & #039;%,13,%&# 039;;

Removing the leading commas, but before any update, lets test what we are doing:

SELECT
parent_id,
user_id,
depth,
asc_path,
TRIM[BOTH & #039;,&# 039; FROM asc_path] AS trimmed_commas
FROM tree

A JSON array is formed around brackets [], and we need to have it in our string to be a valid JSON document:

SELECT
parent_id,
user_id,
depth,
asc_path,
TRIM[BOTH & #039;,&# 039; FROM asc_path] AS trimmed_commas,
   CONCAT[ & quot;
      [ & quot;, TRIM[BOTH & #039;,&# 039; FROM asc_path], & quot;] & quot;] AS added_brackets
FROM tree;

Suggestion : 5

In your question you want to get the values of options array of the JSON in to comma separated string. So the steps of the solution is :,I’ve a JSON column from where I’m extracting values. All goes good, except I’m not able to convert an array inside the JSON Object to a comma separated string. Below is the structure similar to what I have.,I’ve seen some answers that uses nested REPLACE but remember that the value itself may contain square brackets.,Step 1. Get the array data from JSON: Here by using -> operator we are able to fetch the data from data field. data->'$.options' will return the array in JSON format.

Now I’m extracting the data like this:

SELECT
   `id`,
   `name`,
   JSON_UNQUOTE[JSON_EXTRACT[`data`, '$.options']] AS `options`
FROM `my_table`;

Try below query:

select t1.id, t1.name, group_concat[t2.data_]
from test t1
cross join
json_table[t1.data - > '$.options', '$[*]'
   columns[data_ varchar[10] path '$']] t2
group by 1, 2

Step 2. Unnest all the elements of the array: Here JSON_TABLE[Reference] will do the things for us. see below example

select * from json_table[
   '["Option A","Option B","Option C"]',
   '$[*]'
   columns[data_ varchar[10] path '$']] t

Result:

   data_
Option A
Option B
Option C